Unix/Linux Go Back    


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

MySQL join four tables!

Programming


Tags
intersection, join, mysql

Closed    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 01-23-2012   -   Original Discussion by yifangt
yifangt's Unix or Linux Image
yifangt yifangt is offline VIP Member  
UNIX.COM VIP Member
 
Join Date: Sep 2009
Last Activity: 23 May 2018, 3:33 PM EDT
Location: Saskatchewan, Canada
Posts: 530
Thanks: 345
Thanked 10 Times in 9 Posts
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!
Sponsored Links
    #2  
Old Unix and Linux 01-24-2012   -   Original Discussion by yifangt
durden_tyler's Unix or Linux Image
durden_tyler durden_tyler is offline Forum Advisor  
Registered User
 
Join Date: Apr 2009
Last Activity: 9 May 2018, 9:38 PM EDT
Posts: 2,091
Thanks: 23
Thanked 388 Times in 351 Posts


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)
Sponsored Links
    #3  
Old Unix and Linux 01-24-2012   -   Original Discussion by yifangt
yifangt's Unix or Linux Image
yifangt yifangt is offline VIP Member  
UNIX.COM VIP Member
 
Join Date: Sep 2009
Last Activity: 23 May 2018, 3:33 PM EDT
Location: Saskatchewan, Canada
Posts: 530
Thanks: 345
Thanked 10 Times in 9 Posts
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 Unix and Linux 01-24-2012   -   Original Discussion by yifangt
Corona688's Unix or Linux Image
Corona688 Corona688 is offline Forum Staff  
Mead Rotor
 
Join Date: Aug 2005
Last Activity: 23 May 2018, 2:08 PM EDT
Location: Saskatchewan
Posts: 22,677
Thanks: 1,178
Thanked 4,323 Times in 3,986 Posts
What other rows, precisely, would you want from it?
Sponsored Links
    #5  
Old Unix and Linux 01-24-2012   -   Original Discussion by yifangt
yifangt's Unix or Linux Image
yifangt yifangt is offline VIP Member  
UNIX.COM VIP Member
 
Join Date: Sep 2009
Last Activity: 23 May 2018, 3:33 PM EDT
Location: Saskatchewan, Canada
Posts: 530
Thanks: 345
Thanked 10 Times in 9 Posts
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.
Sponsored Links
    #6  
Old Unix and Linux 01-24-2012   -   Original Discussion by yifangt
durden_tyler's Unix or Linux Image
durden_tyler durden_tyler is offline Forum Advisor  
Registered User
 
Join Date: Apr 2009
Last Activity: 9 May 2018, 9:38 PM EDT
Posts: 2,091
Thanks: 23
Thanked 388 Times in 351 Posts
Quote:
Originally Posted by yifangt View Post
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
Sponsored Links
    #7  
Old Unix and Linux 01-24-2012   -   Original Discussion by yifangt
yifangt's Unix or Linux Image
yifangt yifangt is offline VIP Member  
UNIX.COM VIP Member
 
Join Date: Sep 2009
Last Activity: 23 May 2018, 3:33 PM EDT
Location: Saskatchewan, Canada
Posts: 530
Thanks: 345
Thanked 10 Times in 9 Posts
Then the output should be:


Code:
B1 A1 O
B5 A1 O

Sponsored Links
Closed

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Linux More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
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



All times are GMT -4. The time now is 04:09 PM.