CODEWITHSUNDEEP
mysqlsqljoin
Andrej
Andrej

Reputation: 3839

Querying a self referencing junction table

I have the master table (Table 1) with fields UI and name

-----------------
UI      name
-----------------
T001    Organism
T002    Plant
T004    Fungus
T005    Virus
T007    Bacterium
-----------------

and second table (Table 2) with name1 and name2 fields

---------------------
name1       name2
---------------------
Organism    Organism
Organism    Fungus
Plant       Virus
Virus       Bacterium
Organism    Bacterium
---------------------

I need to generate the following output

------------
UI1     UI2
------------
T001    T001
T001    T004
T002    T005
T005    T007
T001    T007
------------

Here is my initial attempt

SELECT * FROM table2 AS t2
JOIN table1 AS t1 ON t2.name1 = t1.name
JOIN table1 AS t3 ON t2.name2 = t3.name;

which, unfortunately, returns wrong number of rows. I wonder how to do joins correctly.

Upvotes: 1

Views: 86

Answers (2)

Madhav Prabhoo
Madhav Prabhoo

Reputation: 326

One possible way is to use two aliases on table1:

select
    t1a.UI as UI1,
    t1b.UI as UI2
from
    table2 t2,
    table1 t1a,
    table1 t1b
where
    t2.name1 = t1a.name and
    t2.name2 = t1b.name;

Upvotes: 1

T.Y. Kucuk
T.Y. Kucuk

Reputation: 477

You can do it by using subquery in select statement of table2.

Here is the code:

select 
(select UI from table1 t where t.name=t2.name1) AS UI1,
(select UI from table1 t where t.name=t2.name2) AS UI2
from table2 t2

Upvotes: 1

Related Questions