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

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

help with multiple tables join

Posted By:      Posted Date: October 02, 2010    Points: 0   Category :ASP.Net

hi all,

i am having trouble with selecting data across tables.

When practitioner login, it has to automatically match the GPNo with the UserName

string name = HttpContext.Current.User.Identity.Name;

SELECT  Practitioner.GPNo FFROM Practitioner WHERE Practitioner.UserName=@UserName

And then, it has to select the date from Consultation Table

SELECT Consultation.Date FROM Consultation WHERE Practitioner.GPNo=Consultation.GPNo

and also the patient first name and last name

SELECT Patient.firstName, Patient.lastName, FROM Patient WHERE Patient.PatientID=Consultation.patientID

is it possible to combine these statement into one statement with JOIN?

thank you

Patient Table 

patientID firstName lastName
Consultation Table



Pracitioner Table  =============
GPNo UserName

View Complete Post

More Related Resource Links

Join two tables on multiple columns


SQL Server 2008 R1

I have been successfully joining 2 tables on multiple fields with TSQL like:

SELECT a.* FROM a JOIN b ON b.field1 = a.field2 AND b.field3 = a.field4

My questions is:

1. Is this the best way to do this?

2. Are there limitations on how many fields you can JOIN ON or the size of these fields?

Now the specifics.

This application is inserting property conveyances (sales) from a source file (CombList), possibly with field corrections (CombListChanges). There are over 500,000 records in the source file each with a matching correction record. The UPDATE (shown below) updates the SourceID of the inserted records (approximately 350,000). The Conveyance INSERT (not shown) and the Conveyance UPDATE (shown below) each took 3-5 minutes to run.

The second query is an INSERT matching the Conveyances with a Real Property record. As written it runs for more than 2 hours and expands the tempdb to 10s of Gigabytes in size. I have never had it successfully execute. However if I run just the SELECT portion and eliminate the deedamount (money) and deedtype (nvarchar(10)) JOINs it runs in about 30 seconds (and gives me slightly too many records). The SourcePage (nvarchar(100)) is made up of a concatenation of several fields and text.

3. Any idea on why this INSERT won't run in a reasonable time?

merging multiple tables in a single dataset to single table


 i have a stored procedure which returns three tables to a dataset ..... now i need to merge all three tables to a single table from d same dataset 

like dataset1 has table1 table2 and table3 .... i want all the three tabels to be merged into dataset1 itself .... instead of three diffrent tables so that i can show all three table data in a single datagrid  as a compact data and combination of 3 tables from d single dataset.....

can some1 help me please.....

Join Two Tables and Prepare Report



            I have a select query which is executing well. Now, I want to add one more field to that query. That field is not in the current query table, It is in the another table.

How do I join those two tables and get that field value in the existing select query.?


SUM col from multiple tables

I am trying to SUM a column from each of 3 tables, where an ID field is the same in each table and the date range is the same also It would look something like this: table1      table2      table3      table4  amt         amt         amt         t4ID  ID           ID           ID  date        date        date SELECT SUM(t1.amt),SUM(t2.amt),SUM(t3.amt) WHERE (each table).ID = @t4ID AND (each table).date BETWEEN @date1 AND @date2 the t4ID, date1 and date2 will be chosen by the user from controls and supplied to the statement via a parameter array. I have tried many variations ( with more to go) to send 1 @t4ID parameter, for example, and use that in all 3 tables (same with the dates). Any help to nudge me in the right direction would be appreciated. Thanks


Hello EveryOne,               I am making a monthly attendance details of all employees who is working in my company. I am using sql server management studio 2005.       My table is in this format. staffcode   name                                   date            Intime   Outtime Duration                         1               Amit                                  01-08-2010    00:00    00:00    00:00 1              Amit                                   02-08-2010    09:52    20:01    10:09 1&n

any alternative for join tables?

 Hi guys, is  there any alternative for joining tables in ado.net? The problem is i have 2 tables: Transaction pkTransactionID TransactionDate FKTransactionCategory : refers to PKCAtegoryID   Category PKCAtegoryID CategoryName   I need to show a joined information from 2 tables in a gridview and be able to update data in tables. I can join them using linq to entity and show them in gridview. But i need to handle update and insert by myself then. Is there any way to make mapping so i can handle the data in those 2 tables as one table and bind it to bindingsource? Thanks in advance.      

Join two tables

I have following SQL command   SELECT     software_communication_comments.observation_comment,child_communication_observation_outcomes.communication_outcome_status, software_community_comments.observation_comment ,child_community_observation_outcomes.community_outcome_status From     child_observation,Child_Basic_Information  ,group_table ,software_communication_comments ,child_communication_observation_outcomes ,software_community_comments,child_community_observation_outcomes  where (child_observation.observation_ID = '2'  and   child_observation.child_ID = Child_Basic_Information.child_ID and group_table.group_ID = Child_Basic_Information.Group_ID) AND (child_communication_observation_outcomes.child_observation_ID = '2' AND software_communication_comments.comment_ID =  child_communication_observation_outcomes.communication_obser_outcomes_ID) AND ( child_community_observation_outcomes.child_observation_ID = '2' AND software_community_comments.comment_ID =  child_community_observation_outcomes.community_obser_outcomes_ID   ) This is working well if both table (child_community_observation_outcomes and child_communication_observation_outcomes) have records belongs to child_observation_ID =2 ,if one table dont have a relevent record it doesn't display anything. I need to ret

Rename a column in multiple Tables using sp_rename

Hi,Any work around for the below issue?Originally "LoadDate" column (see below scripts) was there both in tables DIM_Table_1 and DIM_Table_2According to change in requirement, LoadDate in both the tables to be chnged to LOAD_DATE. It has to be done through script-----------------EXEC sp_rename 'dbo.DIM_Table_1.LoadDate',     'LOAD_DATE', 'COLUMN'         -- Successfully executedEXEC sp_rename 'dbo.DIM_Table_2.LoadDate',     'LOAD_DATE', 'COLUMN'         -- Error: See below Error: The new name 'LOAD_DATE' is already in use as a COLUMN name and would cause a duplicate that is not permitted.-----------------

How 2 join Multiple Keys based table???

I have a table INC with 2 Columns/Fields, i.e. YR and CL set as primary keys by selecting both the columns and selecting primary key symbol with right click. How to set up a FK with the other table INC_DTL's CL which I seek to be restricted to a combination of the INC's 2 fields? Thanx in advance.

Updating multiple tables simultaneously

Hi all, Was wondering if someone can help me out with this multi update problem I have a webform that populates a single table but due to certain dropdown data conditions it could also potentially populate 3 other tables The tables information populates a gridview with amounts from accounts etc.. e.g. Table 1 Site          DataType            account           update 34                  SS                       12.50              N Table 2 Site             account 34                    12.50   Say the above entry has the updated column changed to Y via the webform the amount 12.50 should disappear from the gridview. I was a bit shortsighted and added the additional tables afterward. The information on the gridview pulls from the additional tables and since the first table and the additio

extracting from multiple tables in an odbc call/ssis

Hi,   I have an odbc for DBISAM, and can use it to import into sql server, but only one table at a time.  (when it get's to the choose table screen, it will only let me create a single SQL query.   does anyone know if I can extract from multiple tables using this?   If not - what would I need to change in the DTSX file so that my query goes from "select * from tablea" to "select * from tableb" and the destination table is also "tableb"?   also - can the DTSX file be change to run for multiple tables?

LINQ-to-SQL: How do you join more than 2 tables?

Hi there, I was trying to find out how I can join more than 2 tables using the LINQ-to-SQL syntax. For instance, joining 2 tables in SQL:  SELECT * FROM Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.Key=T2.Column1WHERE T2.Key='17'; can be expressed as: var Result = from T1 in DbContext.Table1 join T2 in DbContext.Table2 on T1.Key equals T2.ForeignKey where T2.Key=17 select new { T1, T2 }; But how would I join 3 or more tables using LINQ? For example: SELECT * FROM (Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.Key=T2.Column1) INNER JOIN Table3 AS T3 ON T3.Key=T2.Column2 WHERE T2.Key='37'; I've been searching and experimenting and I cannot seem to find any informraiton on this. One example I found involves putting the result for the frist join into a temp object, and then performing the second join. I'm not sure performance-wise if that's the same as doing a 3-table join directly using a single SQL statement. Any help will be greatly appreciated! Thanks,- K.  


TABLE 1SELECT I.STUNA    ,S.SNO         ,SUM(B.AMT * Y.YEAR) AS AMT  FROM STUDENT S  LEFT JOIN INFO I ON S.SNO = I.SNO  LEFT JOIN BONUS B ON S.SNO = B.SSNO  LEFT JOIN YEAR  Y ON S.SNO = Y.SSNO  STUNA       SNO      AMTJOHN         A        10LISA         B        20ALLEN        C       100TABLE 2SNO   AMTA     1B     2C     3D     5I WANT TO HAVE RESULT LIKE THIS (TABLE1 + TABLE2)STUNA       SNO      AMTJOHN         A        11LISA         B        22ALLEN        C       103EDDIE        D         5I TRIED USE THIS QEURY SELECT I.STUNA    ,S.SNO         ,SUM(B

Using Multiple Tables in RDLC

I need to use multiples tables in a RDLC report. First table is AuditReport And Second table is CostPerUser But in Report Viewer we can only set dataSource Name to one table like thisReportDataSource dataSource = new ReportDataSource(); dataSource.Name = "DataSet10_AuditReport"; dataSource.Value = ds1.Tables[0]  So how we can use other table...... Pls rply  

Delete from multiple tables

 After trying myriad ways to accomplish deleting from two table, I found a way that actually works. However, AM am sure there is a better way to do this. Any ideas?  Imports System.Data Imports System.Data.SqlClient Imports Telerik.Web.UI Imports System.CodeDom Imports System.Web Partial Class Default2 Inherits System.Web.UI.Page Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim sql As String Dim strConnString As [String] = System.Configuration.ConfigurationManager.ConnectionStrings("CF_SQL_Connection").ConnectionString() sql = "DELETE FROM Table1 WHERE IDTable1 = '5'" Dim connection As New SqlConnection(strConnString) Dim command As New SqlCommand(sql, connection) command.Connection.Open() command.ExecuteNonQuery() command.Connection.Close() Dim sql1 As String Dim strConnString1 As [String] = System.Configuration.ConfigurationManager.ConnectionStrings("CF_SQL_Connection").ConnectionString() sql1 = "DELETE FROM Table2 WHERE IDTable1 = '5'" Dim connection1 As New SqlConnection(strConnString) Dim command1 As New SqlCommand(sql1, connection) command1.Connection.Open() command1.ExecuteNonQuery() command1.Connec

using join when a column may have multiple values

Have 2 tables. Table A has among several columns one called "product_code," which contains 4-digit numerals. Table B has just 2 columns, "product_code," the same 4-digit numerals used in the same column in Table A, and "product_description," which includes a VARCHAR string describing the product referenced by the code. I'm querying Table A and trying to include the "product_description" from Table B with each record returned. Am using a LEFT JOIN like this: SELECT  * FROM Table_A LEFT OUTER JOIN Table_B ON Table_A.product_code = Table_B.prod_code This works fine EXCEPT in cases where Table A has more than one value in "product_code," in which case I get no match in Table B and "NULL" is returned for "product_description." When there is more than one value in the "product_code" column in Table A for a particular record, the values are separated by commas (for example: 1002,1003,9856). How can I get this to work so that for records that have multiple produce codes in table A I get multiple product descriptions from Table B?   Thanks      

Excel data into multiple tables

Hi,       I  have excel file with columns EmpName,Date1,Date2,...Date7 .I want to insert EmpName into Employee table after inserting i will get EmpNo(Identity column) with that  i need to insert Date1,Date2...Date7 into TimeSheet table.    My Excel structure is like this EmpName  8/1/2010      8/2/2010     8/3/2010       8/4/2010    8/5/2010    8/6/2010 8/7/2010 Naresh    17:00-2:00   17:00-2:00   14:00-12:00  7:00-12:00  7:00-12:00  ..               Off Similarly 500 employees data I want to insert name i.e into Emp table after inserting i will get EMpNo i.e identity column with that i should insert Date1(8/1/2010) i.e excel header to date7 into Timesheet table Date column and corresponding time into TimeIn and TimeOut.I will split the time.I just want write Stored procedure for this task. My database tables are like this Emp-EmpNo,EmpName,CompaignId Timesheet-TimeID,EMpNo,Date,TimeIn,TimeOut,TotalHours   Date in Timesheet should be like this   TimeId     EmpNo     Date       TimeIn     TimeOut 
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