Home Man
Search
Today's Posts
Register

Post questions about C, C++, Java, SQL, and other programming languages here.

MySQL join four tables!

Tags
intersection, join, mysql, programming

👤 Login to reply

 
Thread Tools Search this Thread
# 1  
Old 01-23-2012
MySQL join four tables!

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!):
Code:
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:
Code:
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:
Code:
+------+------+------+------+------+------+------+------+
| 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  
Old 01-24-2012
Code:
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

Last edited by durden_tyler; 01-24-2012 at 08:09 AM..
The Following User Says Thank You to durden_tyler For This Useful Post:
yifangt (01-24-2012)
# 3  
Old 01-24-2012
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  
Old 01-24-2012
What other rows, precisely, would you want from it?
# 5  
Old 01-24-2012
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  
Old 01-24-2012
Quote:
Originally Posted by yifangt
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.
...
Not sure I understood that fully, but let's say you had only two tables "tablea" and "tableb". And they had data as follows -

Code:
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  
Old 01-24-2012
Then the output should be:
Code:
B1 A1 O
B5 A1 O

👤 Login to reply

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
UPDATE COmmand post comparing 2 columns in 2 mysql tables siya@ Shell Programming and Scripting 1 06-10-2015 11:37 PM
Show only new and removed records by comparing to MySQL tables gvolpini Shell Programming and Scripting 0 05-30-2012 04:49 PM
Query to join two tables in SQL vanitham Programming 3 08-24-2011 06:22 PM
MySQL: Create a relation between two tables. semash! Programming 2 01-30-2011 03:45 PM
How do you sync tables in mysql between two different servers? muay_tb Web Programming 2 12-21-2009 10:52 AM
create 'day' tables based on timestamp in mysql hazno UNIX and Linux Applications 2 03-10-2009 07:49 AM


All times are GMT -4. The time now is 03:53 PM.

Unix & Linux Forums Content Copyrightę1993-2018. All Rights Reserved.
UNIX.COM Login
Username:
Password:  
Show Password