Reputation: 235
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
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