CODEWITHSUNDEEP
mysqlsql
Bala
Bala

Reputation: 11234

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;

Upvotes: 1

Views: 109

Answers (4)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726499

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

Upvotes: 0

Hlin
Hlin

Reputation: 134

Of course you return nothing! Table A and B inner join returns 1st record of Table A (table A ID = 1) then you join table C, there is no matching rows to join table C, and vice versa.

Upvotes: 0

Joe Enos
Joe Enos

Reputation: 40393

An inner join excludes everything that doesn't match. So after you joined against B, you were left with only one record (id=1). Your inner join against C doesn't have any matches from what's left, so you get nothing.

I suppose a union would do the trick:

select A.* from A join B on a.id = b.pid
union
select A.* from A join C on a.id = c.pid

Or there are other ways, like where a.id in (select pid from b) or a.id in (select pid from c)

Upvotes: 1

Smutje
Smutje

Reputation: 18123

As you first join

1 | Ruby | Table B

and then try to join Table C, there is no match for pid 2 in the aforementioned result, the result is therefore empty.

Upvotes: 1

Related Questions