I have two tables, Table A and Table C, that are related and have a middle table, B, in between. Basically, I am trying to show all the records in both tables A and C, even if some of the records do not relate to each other. At the moment, the only records that are being shown are those that are in the intersection table that shows the many-to-many relationship (Table B).
I have tried right and left joins, but they don't seem to be successful. How can I write a query to show all records, related and unrelated?
A very interesting question, indeed. Let's explore it a little, starting with some sample data:
tableA tableB tableC pkA Name BfkA BfkC pkC Colour 1 tom 1 35 34 red 2 bob 1 36 35 green 3 amy 2 34 36 gray 2 35 37 blue 38 white
Your requirement to "show all the records in both tables even if some of the records do not relate to each other" sounds a lot like what is called a full outer join. However, a join always operates on only two tables at a time:
tableA join tableB join tableC
How do we achieve an "outer" result when there are three tables involved? The answer is to use an outer join for one of the two joins, and an inner join for the other.
"Left outer" three-way query
select pkA, Name, PkC, Colour from tableA left outer join tableB on pkA = BfkA inner join tableC on BfkC = pkC
query result: 1 tom 35 green 1 tom 36 gray 2 bob 34 red 2 bob 35 green 3 amy -- -----
A few observations are in order. Every row from tableA, the left table, is represented in the result at least once. If a tableA row is related to more than one tableC row, then there are that many rows in the result. If a tableA row is related to no tableC row, then there's only one row in the result, and its tableC columns are null.
"Right outer" three-way query
select pkA, Name, PkC, Colour from tableA inner join tableB on pkA = BfkA right outer join tableC on BfkC = pkC
query result: 2 bob 34 red 1 tom 35 green 2 bob 35 green 1 tom 36 gray - --- 37 blue - --- 38 white
The right outer three-way query works the same as the left (except in the other direction, as it were). Every row of tableC, the right table, is represented in the result set at least once. If a tableC row is related to more than one tableA row, then there are that many rows in the result. If a tableC row is related to no tableA row, then there's only one row in the result, and its tableA columns are null.
In both the examples above, one of the two joins is an outer join, and the other is an inner. The reason we can use an inner join for the other join in the many-to-many relationship is because of foreign keys. If a row in the right or left table has a matching row in tableB, the intersection table, then that row in tableB must have a matching row in the other table. It's not a question of nulls, or anything -- it's a matter of relational integrity. If a row of tableB exists, it must have matching primary rows in both tables.
"Full outer" three-way query
Uh oh, trouble.
We cannot do a full outer join between, say, tableA and tableB, because there are no rows of tableB that don't have a match in tableA, as previously explained. Similarly, on the other side, we cannot do a full outer join between tableB and tableC, because there are no rows of tableB that don't have a match in tableC.
This response is continued.