CODEWITHSUNDEEP
sqlsql-server
vikas
vikas

Reputation: 337

how to perform right join on sql query if i have a relation table for 2 master table and want to show all records from both master tables

I have three tables. Table A and Table B. Both are connected with a many-to-many relationship.

Table A:

ID
---
1
2
3

Table B:

ID    
---    
3    
4    
5

Table AB:

ID | A_ID | B_ID
----------------
5  | 1    | 4
6  | 1    | 3
7  | 2    | 3

Now What I need as my results it is like below 

 A_ID | B_ID | Relation_Id 
--------------------------
 1    | 3    |   6
 1    | 4    |   5
 1    | 5    |   Null
 2    | 3    |   7
 2    | 4    |  Null
 2    | 5    |  Null

and so on I need all records from table A then table B and in the last column if they have relation then id otherwise null.

Any help will be great..........

Upvotes: 0

Views: 31

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

What you need is a CROSS JOIN between TableA and TableB so as to get all possible combinations (aka cartesian product).

Then perform a LEFT JOIN to TableAB to find if a relation really exists: 

SELECT a.ID AS A_ID, b.ID AS B_ID, ab.ID AS Relation_Id 
FROM TableA AS a
CROSS JOIN TableB AS b
LEFT JOIN TableAB AS ab ON a.ID = ab.A_ID AND b.ID = ab.B_ID
ORDER BY A_ID, B_ID

Demo here

Upvotes: 3

Related Questions