CODEWITHSUNDEEP
sqlvb.netdatagridview
Shiva
Shiva

Reputation: 235

How to join two tables based on ID and get information

Hello I need some help regarding SQL Query.

Table A

ID      Name     Type 
--------------------- 
1        abc     BC 
--------------------- 
2        def     SD  
---------------------
3        ghi     BC  
----------------------

Table B (BC_ID and SD_ID are Foreign keys of Table 'B' referencing the Primary key 'ID' of Table 'A')

ID     BC_ID    SD_ID
---------------------  
1        1        2  
--------------------- 
2        3        2  
---------------------

I am using VB.Net datagrid view to print the Table B details. I need to print 2 more columns in datagridview which specifies the Name of the corresponding BC_ID and SD_ID. How do I write a SQL query which can get the 'Name' of the Corresponding ID's from the database and print in datagridview...

Expected Output in datagridview:

ID    BC_Name   SD_Name
---------------------   
1       abc        def  
--------------------- 
2       ghi        def   
---------------------

I dont want to have separate BC_Name and SD_Name columns in the table B..I just need to join the two tables and get the name in a single query..Is it possible? Thanks in advance for your help

Upvotes: 0

Views: 3559

Answers (1)

Raging Bull
Raging Bull

Reputation: 18767

You need to join TableA twice with different alias names.

Try this:

SELECT B.ID,ISNULL(A1.NAME,'') as BC_NAME,ISNULL(A2.NAME,'') as SD_NAME
FROM TableB B LEFT JOIN
TableA A1 ON A1.ID=B.BC_ID LEFT JOIN
TableA A2 ON A2.ID=B.SD_ID

Result:

ID  BC_NAME  SD_NAME
1   abc      def
2   ghi      def

See result in SQL Fiddle.

Upvotes: 1

Related Questions