3

I have 3 tables A, B and T

T holds a foreign key to table A and B respectively.

Now I would like to get all rows of (A,B,T) even if T is empty.

SELECT * from

A
LEFT OUTER JOIN T t1 ON t1.A_ID = A.id,
B
LEFT OUTER JOIN T t2 ON t2.B_ID = B.id

WHERE A.B_ID = B.ID

Now the problem is that I get too many rows so I add the following:

AND t1.id = t2.id

But now I get no rows at all which I tried to avoid in the first place via including the LEFT OUTER JOIN.

EDIT: Apart from any sample-data or table-layout my question is merely if a table T with foreign-key-dependencies to more than ONE table in this case A + B needs 2 left-outer-joins as given above or maybe there is another way?

Simply refering to table A in the second left-join like some users suggested cannot work as it is out-of-scope, also explained here
So it works if you surround both tables with parens:

SELECT * from (A, B)
LEFT OUTER JOIN T t ON t.A_ID = A.id and t.B_ID = B.id
Community
  • 1
  • 1
recalcitrant
  • 939
  • 11
  • 23
  • 2
    I may have misunderstood, but what's the purpose of A.B_ID = B.ID? Could you perhaps post table definitions and sample data? – Neville Kuyt Jan 04 '12 at 18:00
  • You may want to try something like this: http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql – Tim Lehner Jan 04 '12 at 18:04
  • For your own sanity (and to an extent, ours) please don't mix `,` and `JOIN` notation. Try using one, preferably `JOIN`. Also, you can () parts of your query, such as `(A LEFT JOIN T ON ???) INNER JOIN (B LEFT JOIN T ON ???) ON ???` – MatBailie Jan 04 '12 at 18:10
  • How can we use "on A.B_ID = B.ID" if this both tables don't have any relation? You given "T holds a foreign key to table A and B". – Somnath Muluk Jan 04 '12 at 18:17
  • Can you provide a bit more info - the table structure and your expected output. Its not clear from the question if A and B are related. If not, do you want a cartesian product? – StevieG Jan 04 '12 at 18:37

3 Answers3

3

Try

select *
from A
join B on A.B_ID = B.ID
left join T on T.A_ID = a.id and T.B_ID = b.id

.. assuming I've understood your question correctly, and you intend a regular, inner join between A and B

rejj
  • 1,216
  • 7
  • 13
  • How can we use "on A.B_ID = B.ID" if this both tables don't have any relation? "T holds a foreign key to table A and B". – Somnath Muluk Jan 04 '12 at 18:16
  • 2
    The `WHERE` clause in the original question implies a relation between A and B – rejj Jan 04 '12 at 18:19
  • +1, although I'm somewhat unsure as to whether the second `ON` clause should connect the two comparisons with `AND` or with `OR`. – Andriy M Jan 04 '12 at 21:00
3

Try this query: If A and B no any relation:

SELECT * FROM
(A, B)
LEFT OUTER JOIN T ON T.A_ID = A.id
AND T.B_ID = B.id

And if A and B have relation add this line at last:

WHERE A.B_ID = B.ID
Somnath Muluk
  • 55,015
  • 38
  • 216
  • 226
0

If I've understood right, then maybe this will work for you?

SELECT * from
A
LEFT OUTER JOIN T t1 ON t1.A_ID = A.id,
B
LEFT OUTER JOIN T t2 ON (t2.B_ID = B.id AND t1.id = t2.id)

WHERE A.B_ID = B.ID
  • This is the problem, I am trying to solve: t1.id is unknown in the second LEFT OUTER JOIN. – recalcitrant Jan 04 '12 at 18:29
  • OOps, Sorry, didn't notice the comma... Maybe then SELECT * from A LEFT OUTER JOIN T t1 ON t1.A_ID = A.id LEFT OUTER JOIN B ON (t1.B_ID = B.id AND A.B_ID = B.ID) will help –  Jan 04 '12 at 18:38
  • but still, what relation is between **A** AND **B**? –  Jan 04 '12 at 18:49