Bala Bala - 3 months ago 16
SQL Question

How do I inner join multiple tables?

I have tables A, B and C and I want to get matching values for from all tables (tables have different columns).

Table A (primary key = id)
+------+-------+
| id | name |
+------+-------+
| 1 | Ruby |
| 2 | Java |
| 3 | JRuby |
+------+-------+
Table B (pid is reference to A(id) - No primary key)
+------+------------+
| pid | name |
+------+------------+
| 1 | Table B |
+------+------------+
Table C (primary key = id, pid is reference to A(id))
+------+------+------------+
| id | pid | name |
+------+------+------------+
| 1 | 2 | Table C |
+------+------+------------+


So my below query returned nothing. Whats wrong here? Is it treated as AND when multiple inner joins present?

Select A.* from A
inner join B ON a.id = b.pid
inner join C ON a.id = c.pid;

Answer

When you inner-join like this, a single row from A needs to exist such that a.id = b.pid AND a.id = c.pid are true at the same time. If you examine the rows in your examples, you would find that there is a row in A for each individual condition, but no rows satisfy both conditions at once. That is why you get nothing back: the row that satisfies a.id = b.pid does not satisfy a.id = c.pid, and vice versa.

You could use an outer join to produce two results:

select *
  from A 
  left outer join B ON a.id = b.pid
  left outer join C ON a.id = c.pid;

a.id   a.name  b.pid   b.name    c.id   c.pid  c.name
  1  | Ruby  |   1   | Table B | NULL | NULL |  NULL
  2  | Java  | NULL  |  NULL   |  1   |   2  | Table C