Search code examples
mysqlselectnatural-join

Natural JOIN with two foreign keys

i have those two tables 

id | name |
___________
 1 | John |
 2 | Mike |

id | id_name1 | id_name2
________________________
 1 |     2    |    1
 2 |     1    |  null

first tables primary key is ID in second table i have two foreign keys ID_NAME1 and ID_NAME2 that they reference to primary key from first table. After using 

SELECT table1.name, table2.id FROM table1 NATURAL JOIN table 2

i get 

John 1
John 2
Mike 1
Mike 2

but i want 

John 1
John 2
Mike 2

what am i doing wrong?

Solution

  • You don't need natural join what you expect I guess is:

    http://sqlfiddle.com/#!9/c9b1d/7

    SELECT table1.name, table2.id
FROM table1
LEFT JOIN table2
ON table1.id = table2.id_name1
  OR table1.id = table2.id_name2
  ORDER BY table1.id,table2.id