.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

Join two tables

Posted By:      Posted Date: September 02, 2010    Points: 0   Category :Sql Server
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

View Complete Post

More Related Resource Links

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.?



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.      

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

Join two fact tables




I have the following two fact tables, upon which a cube was built. As you can see, these two tables have most of the common (shared) dimensions except for ProductTypeID (product dimension) on fct_productsales table. The relationship between these two fact tables is 1(fct_loansales) to Many (fct_productsales).

When I browse these measures from the cube, they look fine (and break down perfectly) through all the shared dimensions.

However, when I try to filter or browse through product dimension (along with other dimensions), the measure values from fct_productsales will filter down correctly, but the values from fct_loansales does not change.  I tryied to create degenerate dimension from fct_loansales, but no luck. Any help is appreciated.



AccountNumber                       char

Join Tables For Definitions (Data Table/Definition Table)



I am well versed with Join/Outer Join/Inner Join. My question is not related to normal Joining of two tables. Its little complex. Let me see if I can explain well.

There are two tables, were data is stored.

Name_Info : nvarchar(10)
Date_Info : smalldate()
Spec_Info_1 : tinyint
Spec_Info_2 : tinyint
Spec_Info_3 ; tinyint

Name_Data : nvarchar(10)
City_Data : nvarchar(10)
State_Data : nvarchar(10)

To join these two tables, I use the following query (joined on Name_Info ON Name_Data)


SELECT Name, Date=CONVERT(varchar, Date, 101), City, State,
CASE Spec_Info_1 WHEN '0' THEN 'House' WHEN '2' THEN 'Shop' WHEN '3' THEN 'Bay' ELSE '-' END AS Spec_Info_1, 
CASE Spec_Info_2 WHEN '0' THEN '10 Feet' WHEN '2' THEN '20 Feet' WHEN '3' THEN '40 Feet' ELSE '-' END AS Spec_Info_2, 
CASE Spec_Info_3 WHEN '0' THEN '2 Windows' WHEN '2' THEN '4 Windows' WHEN '3' THEN '6 Windows' ELSE '-'  END AS Spec_Info_3
FROM Table_Spec_Data LEFT OUTER JOIN Table_Name_Data On Table_Spec_Data.Name_Info=Table_Name_Data.Name_Data  

SQL - Join Tables on Column Name and Row Field Value



I have two SQL tables (SQL 2005) that I need to join but am in need of some assistance. Essentially I need to join a column name with row field values.

The first table contains the following columns

  • Loan ID
  • SSN
  • Ord_SignedApp
  • Ord_Title
  • Ord_Reg
  • Ord_Name

The second table contains the following columns

  • Item
  • Ord

However, the rows in the second table for the Ord column contain the following values:

  • Ord_SignedApp
  • Ord_Title
  • Ord_Reg
  • Ord_Name

I need to join the row field value of the second table with the column name of the first table. I want to display the values of the respsective columns of table one (Ord_SignedApp, Ord_Title, Ord_Reg, Ord_Name) and place them in a new column. These values are represented as dates

Any assistance you can provide would be much appreciated.

Thank you for your help,




need to join two tables and also convert top 3 rows to column


Hi, I have two tables, CUSTOMER and PAYMENT_DETAILS. I need to extract "name" and "phone" from CUSTOMER table and TOP 2 payments for matching email from PAYMENT_DETAILS table. Kindly refer to the attached image for table structure and Result required.




Performance tuning > two tables with strings > best query to join them


I have two subqueries which build a string (varchar(200)), first subquery creates 200000 records and second 50000 records, how can I write a select statement to get records from subquery first with string existing in second subquery records.

Will sorting both the sub query results be helpful? Kindly advice for best performing select statement.

How to get all data from tables when inner join is not enough


Suppose I have two tables "t1" and "t2"

t1 :
id  FirstName  Age
1   Joe        22
2   James      33
3   Bart       28
id  LastName  
1   Coymer
2   Manes

I can combine the data from the two tables using an inner join.

1  Joe    22  CoyLap
2  James  33  Manes

The problem though is that the person "Bart" with an Id of 3 is
left out because he does not have an entry in the second table.
So how can I include Bart in the output in such a way that the output
might look like this:

1  Joe    22   CoyLap
2  James  33   Manes
3  Bart   null null

Or spaces can instead appear for Barts last name and age instead of null.
Basically I just need Bart in the output as well even though
he has no age and last name.

Entity Framework - how to join tables without LINQ and with only string?


Hi all,

I have a question about Entity Framework. Please answer if you know answer on this. I have such query :

    String queryRaw =
        "SELECT " +
        "p.ProductName AS ProductName " +
        "FROM ProductEntities.Products AS p " +
        "INNER JOIN CategoryEntities.Categories AS c " +
        "ON p.CategoryID = c.CategoryID ";
    ObjectQuery<DbDataRecord> query = new ObjectQuery<DbDataRecord>(queryRaw, entityContext);
    GridView1.DataSource = query;

Particularly I want to join few tables in one query, but I can NOT use LINQ and can NOT use ObjectQuery with objects mapped to DB fields inside my query. Because each entity creates dynamically. So this is what i can NOT use :



The question is can I use something like this instead of using objects?

query.Join ("INNER JOIN CategoryEntities.Category ON p.CategoryID = c.CategoryID ");

help with multiple tables join


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

How does SSAS join tables in DSV from different servers?


Running into an issue with a dimension...we have 1 table thats on 1 server joining to another table on another server. We need to build a dimension off 1 table and include an attribute from the other table.

When we do this we get an error that says "SQLCNI10.1" is not registered. This join works fine until we include a column from the second table into the dimension.

The first table is on a 2008 instance and the second table is on a 2005 instance. What I am guessing its doing is going to the 2005 server and using a remote query to get to the 2008 server.

I think the fix is to install the 2008 native client on the 2005 server...but is there any other way around this?


Union all in SSIS join two tables row wise or column wise

Union all in SSIS join two tables row wise or column wise

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?

joining 3 tables and using join to only include records not found in other table



I have 3 tables. I would like to get the number of records in table A that do not have cooresponding co_asset_ids in TABLE C but also need
to be be found in TABLE B and where the asset_id = 3. Said another way: If TABLE A record is in TABLE B and does not find a co_asset_id of 3 as found in table C then show these records.

assetno 4 and 5 are irrelevant but added to fill out the data more.

I know I could do this getting the co_asset_id column into TABLE A and then do some joins between TABLE A and TABLE C and leave TABLE B out, which I have done in the past. I would like to finally know if this can be done by using the 3 tables together in one elegant query.

The result of this query for this example data  would be:

Table A

Table B
assetno    co_asset_id
1              11
2              22
3              33
4              44
5              55


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