.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

retrive details records as a field in master table

Posted By:      Posted Date: October 09, 2010    Points: 0   Category :Sql Server

I have 2 tables:

Student table with Id, Name fields

Phone table with Id,StudentId,PhoneNumber

Now, I want to show student list with all it's phone numbers in 1 row.

this query: "select student.*, phone.* from student inner join phone on student.id=phone.studentid" shows me the list of students with duplicate per each phone number.

I want something like this:

1 paul       66665566,7766665

2 ben        5566556,8877667

3 sam       4444334,3454332,7656765

View Complete Post

More Related Resource Links

Please on how to check updated records in a master table.


I have two tables. One is holding records temporarily while the other is master table. I am trying come up with a sql that will be picking a record at a time from temporal table then check is if the record has been update dated in the master table. If it has been updated it does nothing else it updates. Now I am having challenges. The table fields are as follows: Please help


Master table

Id   Firstname  Surname  Location  Amount   status  Position




Temporla  Table

Id   Firstname  Surname  Location  Amount   status  Position

adding XML 'records' with a key field


I have a vb.net Sub that is successfully adding 'records' to my XML file:

Private Sub btnSubmit_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles btnSubmit.Click             'for adding an XML 'record'
        Using ds As New DataSet()
            Dim dr As DataRow = ds.Tables(0).NewRow()
            dr("subject") = txtNewEvent.Text
            dr("year") = DateTime.Now.ToString("MM/dd/yy")
            txtNewEvent.Text = ""

Business object design in master details.


Hello guys, I have the following scenario,

  1. There is a master business object containing 40 fields
  2. 2 separate child business objects
  3. If I update a child record, I have to update master's 'Last modify by' status along with 3 or more fields in master
  4. For this purpose, I don't want to initialize a separate Master class object (as most of the fields are unused/wasted)
  5. The 'last modify by' also gets updated if the records in master get updated themselves

Currently I have separate business objects for master table, Child 1, Child 2 etc.

How do I control this in my business object design ? Do I break up my master object ? Say I extract last 'modify by ' into a new class, through inheritance or some other way ?

Is there a way that whenever child gets updated I use a 'small' class to update Master status only and whenever the master itself gets updated I do the same.

Problem is that I am having redundancy in my small class (Master class also contains 'Last Modify By ',

What would you advice. Please have your say. Thank you.

Edit  I am using webforms guys.

Master Table

My application consist of region,compaign(IBM,Microsoft,Dell),Line Of business(lob),weekly schedules of employees,Employee Data.User(Company Authorityuploads weekly schedules of the employee by selecting region,Compaign and LOB in dropdownlist. Should i need to use master table for this,if yes can i use same master for the all the compaigns.One authority may upload dell copmaign data with 500 employees schedule,another authority may upload microsft compaign schedule. Give me table struture for this. EmpNo,EmpName,Monday(date and tiimings in and out).sunday,Region,Compaign and LoB

Client Object Model - cannot retrive "Modified By" field value

Hi, I am using Client Object Model to get all items from a list and display theme with "Modified By" information. Everything works nice until I start using test users. I have noticed that for user who is in Domain Admins group i have no problem for others I cannot read "modiefied by" field value. var context = ClientContext.Current; activitySPList = context.Site.RootWeb.Lists.GetByTitle("Activities") activityItems = activitySPList.GetItems(CamlQuery.CreateAllItemsQuery()); context.Load(activityItems); context.ExecuteQueryAsync( ClientRequestSucceeded, ClientRequestFailed ); All items in activityItems collection doesn't have "Modified By" fields. Do you know why and how to fix it? Thanks for your help.

Duplicate records find in table

  Hi ALL, I want to find the dynamically check the primary key values and remove the duplicate records.. please suggest it.   Regards Ram

Selecting different no. of records from the same table

Hello all, This might seem a basic thing but bothering me from last few days. Suppose I have a table containing 5 million records and I am running queries one by one (one query executes completly and then next query is fired)as below on that table. SELECT top 20000 FROM MyTable SELECT top 200000 FROM MyTable SELECT top 2000000 FROM MyTable  Now my questions are; Will SQL Server use same execution plan? As I am running these queries one by one in same order as given above, will second query take less time than it should if run standalone (only second query is fired, no query fired before that) and similarly will third query take less time than it should if run standalone (only third query is fired, no query fired before that), since there is a possibility of same execution plan being used?  Regards.    

I have relation many to many - how can I delete records from intermediate table ?

I have tables: Books id title Authors id name BooksAuthors book_id author_id Entity Framework generated only models for tables Books and Authors in relation many to many. How can I delete from table BooksAuthors all records where book_id =5 ?

Get the count of the recently added records to a table

i need to create 2 temporary tables. The records inserted into the 2nd table is the same as that from table1. Now, i am inserting some other records into table 2.what i need is, to retrieve the count of the recently added records into table2.How to achieve this? 

Cannot Insert records in table using NHibernate

I am very much new with NHibernate. I am inserting a new record in the table. However, it seems that it does not insert. What I'm using is Fluent NHibernate.Below is the code which inserts records. public virtual void Save(string CreatedBy) { ISession NHSession = Star.CNPL_BusinessObjects.Common.Session.OpenSession(); ITransaction NHTransaction = NHSession.BeginTransaction(); try { this.Save(CreatedBy, NHSession); NHTransaction.Commit(); NHSession.Flush(); } catch (Exception ex) { NHTransaction.Rollback(); throw ex; } } public virtual void Save(string CreatedBy, ISession Session) { try { if (this.IsPersisted) { this.SaveAuditTrail(Session, false, CreatedBy); Session.SaveOrUpdateCopy(this); } else { Session.Save(this); } } catch (Exception ex) { throw ex; } }

Upload millions of file records to a table

I have six input files having millions of record. I need to upload these data to my table. How can I upload the details in the most efficient way. Any help on this will be greatly appreciated.

importing table with Access "memo" field to SQL

I imported an access database using the DTS wizard, into SQL 2005. There is a field in one of the Access tables that is a memo field, filled with a bunch of text including linebreaks. After importing, the field is indicated as the nvarchar(MAX) datatype when I check in SQL Enterprise Manager. The text in that field is displayed without line breaks, both in EM and when viewed on a web page. I changed the datatype of that column to "text" and now can see line breaks when I hover over a value in that column while viewing the table data in Visual Studio 2010's Server Explorer.But when I display the data in a ListView, there are no linebreaks. When I view my TableAdapter in my dataset, there are no linebreaks either.I must be missing something here? Thanks. 

Problem with listing records from table (object not found)

Hi everyone, I'm creating a website based on ASP.NET MVC 2 and SQL Server 2008 R2 Express for the database. After creating the table in database, I want to list all records in that table. For that, I use "scaffold'ing in ASP.NET to automatically generate the view file. But I encountered an error when loading that page. The error message was: [CODE] Server Error in '/' Application. Invalid object name 'dbo.Accommodations'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'dbo.Accommodations'. Source Error: Line 41:         </tr> Line 42: Line 43:     <% foreach (var item in Model) { %> Line 44: Line 45:         <tr> Source File: d:\MyProject\Views\Accommodation\Index.aspx    Line: 43 Stack Trace: [SqlException (0x80131904): Invalid object name 'dbo.Accommodations'.]    System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +2030802    System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5009584 &n

How can I get the cumulative sum of a field in a table

For Eg.   I have a table like gias given below: Name                  Amount ------------------------------ aaa                       10 bbb                      20   and I want an output like one given below on running SQL query or stored procedure Name                  Amount                  cumulative amount --------------------------------------------------------------------- aaa                       10                              10 bbb                      20                              30   can anyone plz help me on this

how to return records in squence of inner join table?

Hi, I have test database with following script. I am trying to explain my problem with this sample db script. I am creating a temp. table with the ordered column from other table and then using that table to join the other table. If you notice the output of the below select query, the returned rows from first table are in the sequence of insertion not in the sequence of the temp. table. Is there any other way to retrieve rows in the sequence of temp. (joined) table? CREATE TABLE [dbo].[Table_2]( [c1] [int] NULL, [c2] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (1, N'z') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (2, N'y') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (3, N'x') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (4, N'a') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (5, N'b') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (6, N'c') CREATE TABLE [dbo].[Table_1]( [c1] [int] NULL, [c2] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (3, N'x') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (2, N'y') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (1, N'z') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (6, N'c') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (5, N'b') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (4, N'a&

xml as text will not save in table field in some cases

I have a stored procedure that saves 4 fields into a data table named xml.  One of the fields is called xml, and is just a text field.  Usually, my sp properly stores the data, but occassionally the xml portion of my record is not stored.  It's on a remote server, so I'm having trouble hooking up a debugger to it, but I may have identified some charactoristics of the data in the cases where the data is not saving into the field.  I think it has a lot of "......."s mixed in with the xml.  I'm wondering if SQL could be identifying this as some kind of security threat.  Any thoughts would be super appreciated. 

Master Page and table layout

Hi, i am in the process to learn create master page using HTML table layout.I find out many resources online that use table to design their master page layout.I have 1 thing that i do not undestand:all of them using heigh= n pixel to control their footer, header, content  and navigation menu.My concern is that by doing so, the master page is fixed to only particular resolution only.In future there might be higher resolution available, the master page design need to be changed from time to time which is considered not so good programmer.OR in future, the when site need to have something like "Best view in resolution 1024 x 768", somethign like that...Is there any other way not to do so? Are they doing their master page correctly?
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend