MySQL join four tables!

Applications Programming
1

Hello;
I want merge four MySQL tables to get the intersection that have a common field for all of them. Join two tables is fine to me, but my this case is different from common situations and there are not very many discussions about it. Can anybody give me some idea? Thanks a lot!
Here is part of my simplified tables (out of ~30000 rows per table and more than 10 columns of complicate formats, so that I do not want use shell command JOIN or other want to do the job!):

TableA:
A1   O
A2   P
A3   Q
A4   R
A5   S
A6   T

TableB:
B1   A1
B2   A2
B3   A3
B4   A4
B5   A1
B6   A2

Table C: 
C1   A1
C2   A2
C3   A2
C4   A4
C5   A4
C6   A1

TableD
D1   A1
D2   A2
D3   A3
D4   A4
D5   A5
D6   A1

The output is expected to be:

B1   A1   C1   A1   D1   A1   O   
B2   A2   C2   A2   D2   A2   P
B4   A4   C4   A4   D4   A4   R

My Code is:

SELECT
B.ID, B.AGI,
C.ID, C.AGI,
D.ID, D.AGI,
A.ID, A.AGI FROM TableB B, TableC C, TableD D, TableA A WHERE
B.AGI = A.ID
AND C.AGI = A.ID
AND D.AGI = A.ID
AND B.AGI = C.AGI
AND D.AGI = C.AGI
AND B.AGI = D.AGI;

But the output is not what I expected:

+------+------+------+------+------+------+------+------+
| ID   | AGI  | ID   | AGI  | ID   | AGI  | ID   | AGI  |
+------+------+------+------+------+------+------+------+
| B1   | A1   | C1   | A1   | D1   | A1   | A1   | O    |
| B5   | A1   | C1   | A1   | D1   | A1   | A1   | O    |
| B1   | A1   | C6   | A1   | D1   | A1   | A1   | O    |
| B5   | A1   | C6   | A1   | D1   | A1   | A1   | O    |
| B1   | A1   | C1   | A1   | D6   | A1   | A1   | O    |
| B5   | A1   | C1   | A1   | D6   | A1   | A1   | O    |
| B1   | A1   | C6   | A1   | D6   | A1   | A1   | O    |
| B5   | A1   | C6   | A1   | D6   | A1   | A1   | O    |
| B2   | A2   | C2   | A2   | D2   | A2   | A2   | P    |
| B6   | A2   | C2   | A2   | D2   | A2   | A2   | P    |
| B2   | A2   | C3   | A2   | D2   | A2   | A2   | P    |
| B6   | A2   | C3   | A2   | D2   | A2   | A2   | P    |
| B4   | A4   | C4   | A4   | D4   | A4   | A4   | R    |
| B4   | A4   | C5   | A4   | D4   | A4   | A4   | R    |
+------+------+------+------+------+------+------+------+
14 rows in set (0.00 sec)

Can anybody give me some idea? Got lost with the LEFT/ RIGHT/INNER JOINs. Expert suggestion can save me days of scratch. Thanks a lot!

2 
mysql>
mysql>
mysql> select * from tablea;
+----+-----+
| id | agi |
+----+-----+
| A1 | O   |
| A2 | P   |
| A3 | Q   |
| A4 | R   |
| A5 | S   |
| A6 | T   |
+----+-----+
6 rows in set (0.00 sec)

mysql>
mysql> select * from tableb;
+----+-----+
| id | agi |
+----+-----+
| B1 | A1  |
| B2 | A2  |
| B3 | A3  |
| B4 | A4  |
| B5 | A1  |
| B6 | A2  |
+----+-----+
6 rows in set (0.00 sec)

mysql>
mysql> select * from tablec;
+----+-----+
| id | agi |
+----+-----+
| C1 | A1  |
| C2 | A2  |
| C3 | A2  |
| C4 | A4  |
| C5 | A4  |
| C6 | A1  |
+----+-----+
6 rows in set (0.00 sec)

mysql>
mysql> select * from tabled;
+----+-----+
| id | agi |
+----+-----+
| D1 | A1  |
| D2 | A2  |
| D3 | A3  |
| D4 | A4  |
| D5 | A5  |
| D6 | A1  |
+----+-----+
6 rows in set (0.00 sec)

mysql>
mysql>
mysql> -- query
mysql> select b.id, b.agi,
    ->        c.id, c.agi,
    ->        d.id, d.agi,
    ->        a.agi
    ->   from tablea a,
    ->        (select agi, min(id) as id from tableb group by agi) b,
    ->        (select agi, min(id) as id from tablec group by agi) c,
    ->        (select agi, min(id) as id from tabled group by agi) d
    ->  where a.id = b.agi
    ->    and a.id = c.agi
    ->    and a.id = d.agi;
+------+-----+------+-----+------+-----+-----+
| id   | agi | id   | agi | id   | agi | agi |
+------+-----+------+-----+------+-----+-----+
| B1   | A1  | C1   | A1  | D1   | A1  | O   |
| B2   | A2  | C2   | A2  | D2   | A2  | P   |
| B4   | A4  | C4   | A4  | D4   | A4  | R   |
+------+-----+------+-----+------+-----+-----+
3 rows in set (0.00 sec)

mysql>
mysql>
mysql>

tyler_durden

1 Like
3

I was thinking if it is correct algorithm to just get the three rows instead of 14 rows. Any comments Tyler?
Thank you again!

4

What other rows, precisely, would you want from it?

5

What I wanted is to pick up all rows of the three tables (B, C & D) by the common key from col1 of TableA, i.e. A1 ~ A6, if B, C & D share any key of each col2 of B, C & D. The 14 rows output seems to be correct, but with exhaust combination of the four tables.
The fact for this study is to pick up all the common genes of the three organisms with Arabidopsis (model plant) as the reference(key).
At first I was thinking about the technique part to retrieve common rows of four tables (3 tables at least). Now I need to think it over after Tyler's code for my purpose, as I did not take the redundant items (key) in B, C & D although items of col1 in TableA are unique.
Thank you anyway.

6

Not sure I understood that fully, but let's say you had only two tables "tablea" and "tableb". And they had data as follows -

mysql>
mysql>
mysql> select * from tablea;
+----+-----+
| id | agi |
+----+-----+
| A1 | O   |
+----+-----+
1 row in set (0.00 sec)

mysql>
mysql> select * from tableb;
+----+-----+
| id | agi |
+----+-----+
| B1 | A1  |
| B5 | A1  |
+----+-----+
2 rows in set (0.00 sec)

mysql>
mysql>
mysql>

What would have been your expected output in that case?

tyler_durden

7

Then the output should be:

B1 A1 O
B5 A1 O
8

If you extend this requirement to the 4 tables and the original data in them, then your query in your first post is correct.
You should see 14 records as per your expectation here.

tyler_durden

9

Thanks a lot, Tyler! I thought so now and really appreciate your input.