MySQL join data from same table twice

Question

I have table info with 3 rows: id,monter1,monter2. Monter1 and monter2 are id's from table monterzy (id, name, surname) that i want to join with. The problem is i cant make a proper query to pick up these values then in php. Currently i have:

SELECT i.id
     , m.name
     , m.surname 
  FROM info i
  JOIN monterzy m
    ON i.monter1 = m.id;

I want to expand it to also get monter2 and corresponding name and surname. I have searched on google, there were examples with AS but i have no idea how to do it. Thanks!

Answer 1

You need to join the same table twice with different alias names

SELECT info.id, 
       m1.name as m1_name, m1.surname as m1_surname,
       m2.name as m2_name, m2.surname as m2_surname
FROM info 
INNER JOIN monterzy m1 ON info.monter1 = m1.id
INNER JOIN monterzy m2 ON info.monter2 = m2.id

Answer 2

You just need to alias the tables. That is, with each join of the same table give it a different alias (name) so that the rest of the query knows which one is which. Something like this:

SELECT
  info.id,
  m1.name,
  m1.surname,
  m2.name,
  m2.surname
FROM
  info
  INNER JOIN monterzy AS m1 ON info.monter1 = m1.id
  INNER JOIN monterzy AS m2 ON info.monter2 = m2.id

Notice how each join has an AS someAlias to give the joined table a new name just for the purposes of this query. And how the rest of the query references that alias instead of the table name.

Answer 3

You need to use table aliases for this :

SELECT info.id, m1.name as name1, m1.surname as surname1, m2.name as name2, m2.surname as surname2
FROM info 
INNER JOIN monterzy m1 ON info.monter1 = m1.id
INNER JOIN monterzy m2 ON info.monter2 = m2.id;

Answer 4

You can use left join

SELECT
  info.id,
  m1.name,
  m1.surname,
  m2.name,
  m2.surname
FROM
  info
  INNER JOIN monterzy AS m1 ON info.monter1 = m1.id
  LEFT JOIN monterzy AS m2 ON info.monter2 = m2.id