How to do a Join in MySQL to Several Columns in Same Table

Ever found yourself in a situation where you had to join to the same column(s) within a single table more than once?

It may seem hard to do at first glance, but it isn't, really, if you use aliasing. Aliasing allows you to give a table or column a temporary name -- usually one that is shorter or more intuitive -- during a query to make the query easier with which to work. (If you're not too familiar with aliases, here's a good SQL alias lesson at w3schools and a good best practices article.)

Remember that a join lets you virtually connect two or more tables during a query so you can extract required data; you also can join a table to itself (i.e., self-join) if that table contains all of the columns needed to complete the query. A particularly tricky self-join is one that requires you to refer to the same column(s) in the same table multiple times. This is because the query will return an error if you use the same column name more than once. Even when referring to the same column, you can meet the requirement that each reference to the same column(s) be unique by using a different alias for each reference made to that column(s).

Here's an example of how I did just such a thing. I had to update a webpage that showed the four people assigned to each account listed and convert their names from static text to clickable email links. To do this, I joined two tables, accts -- which held the assignment data -- and employee, which had the email addresses , on on the first name, last name, email columns in employee. I had to self-join to each of those columns four times, once for each assigned person. So I did the following query to feed the PHP variables used in the mailto HTML:

SELECT
  a.*,
  t.first_name, t.last_name, t.email AS t_email,
  u.first_name, u.last_name, u.email AS u_email,
  m.first_name, m.last_name, m.email AS m_email,
  d.first_name, d.last_name, d.email AS d_email
FROM
 accts AS a
LEFT JOIN
  employees AS t ON CONCAT(t.first_name, ' ', t.last_name)= a.t
LEFT JOIN
  employees AS u ON CONCAT(u.first_name, ' ', u.last_name)= a.u
LEFT JOIN
  employees AS m ON CONCAT(m.first_name, ' ', m.last_name)= a.m
LEFT JOIN
  employees AS d ON CONCAT(d.first_name, ' ', d.last_name)= a.d;
ORDER BY
agency;

1 comment:

Note: Only a member of this blog may post a comment.