daninthemix daninthemix - 4 months ago 6
SQL Question

MySQL JOIN two tables in two places

This is my query:

SELECT a.id, a.hostname, a.asset_tag, d.model, b.location,
c.type, a.earmarked, a.earmarked_office, a.earmarked_user,
a.earmarked_date, a.earmarked_by
FROM laptops a
JOIN locations b ON a.location = b.id
JOIN types c ON a.type = c.id
JOIN models d ON a.model = d.id
WHERE b.stock = 1


Now, the
a.earmarked_office
field is actually a reference to an ID in the
locations
table, which we're already using to fill in
b.location
. How can I, in the same statement, retrieve the actual location name for
earmarked_office
?

Answer

Join to the table twice:

SELECT lt.id, lt.hostname, lt.asset_tag, d.model, l.location,
        c.type, lt.earmarked, lt.earmarked_office, lt.earmarked_user,
        lt.earmarked_date, lt.earmarked_by,
        lteo.??
FROM laptops lt JOIN
     locations l
     ON lt.location = l.id JOIN
     types t
     ON lt.type = t.id JOIN
     models m
     ON lt.model = m.id JOIN
     locations leo
     ON lt.earmarked_office = leo.id
WHERE l.stock = 1;

Notes:

  • Using table aliases is good. However, use table aliases to make it easier to follow the logic of the query.
  • I don't know what columns you want from the second table.
  • Use as to rename the columns, so they don't conflict with the columns already coming from l.
  • You may need a LEFT JOIN, if there are unmatched values.