How do I make my MySQL query with joins more concise?
- by John Hoffman
I have a huge MySQL query that depends on JOINs.
SELECT m.id, l.name as location, CONCAT(u.firstName, " ", u.lastName) AS matchee, u.email AS mEmail, u.description AS description, m.time AS meetingTime
FROM matches AS m
LEFT JOIN locations AS l ON locationID=l.id
LEFT JOIN users AS u ON (u.id=m.user1ID)
WHERE m.user2ID=2
UNION
SELECT m.id, l.name as location, CONCAT(u.firstName, " ", u.lastName) AS matchee, u.email AS mEmail, u.description AS description, m.time AS meetingTime
FROM matches AS m
LEFT JOIN locations AS l ON locationID=l.id
LEFT JOIN users AS u ON (u.id=m.user2ID)
WHERE m.user1ID=2
The first 3 lines of each sub-statement divided by UNION are identical. How can I abide by the DRY principle, not repeat those three lines, and make this query more concise?
