Joining 3 tables with mySQL with 1 query

Question

This has been asked and answered in different instances but all that I've seen doesn't quite work for my problem.

PROBLEM: I have 3 tables I need to pull data from at the same time to compare and retrieve information from. All 3 tables contain an 'email' column. Now, a user's email from table1 may match same user's email in both table2 AND table3, depending on user's status. OR an email from table1 will ONLY match either an email in table2 or table3, depending on user's status again. For example, a user may have a red status (user will show in table2), a blue status (user will show in table3), or both, red and blue (user will show in both, table2 and table3).

WHAT IS NEEDED: an email from table1 needs to be compared to email in table2 and table3 and return a region value for a given user, which is recorded in table2 and table3 but not in table1. I know. Delightful data architecture! Either way, I was able to JOIN table1 to table2 very successfully but I am not sure how to slap on a JOIN with table3.

Here's the query for 2 tables:

SELECT * FROM table1 INNER JOIN table2 ON table2.email = table1.email WHERE month = 'numberHere' ORDER BY submitdate DESC

When I simply add another INNER JOIN my code doesn't break per say but it doesn't give me any rows to be displayed either. So the code below doesn't work despite the many working examples from the web:

SELECT * FROM table1 INNER JOIN table2 ON table2.email = table1.email INNER JOIN table3 ON table3.email = table2.email WHERE month = 'numberHere' ORDER BY submitdate DESC

you need to post your table structure for all 3 tables

cmorrissey
– cmorrissey

2016-01-26 21:55:05 +00:00
Commented Jan 26, 2016 at 21:55 — cmorrissey
– cmorrissey, Commented Jan 26, 2016 at 21:55

fusion3k · Accepted Answer · 2016-01-26 22:11:15Z

To match the row from table1 in any case and the rows of other tables only if they are, you have to use LEFT JOIN, joining table2 and table3 with table1 (not table2 with table1 and table3 with tabel2):

SELECT * FROM table1 LEFT JOIN table2 ON table2.email = table1.email LEFT JOIN table3 ON table3.email = table1.email

In this way you select values from table1 even without a match in other tables.

See more about SQL Joins: Different SQL Joins

Back to studying SQL I go. ;p Thanks a lot. Worked perfectly.

Barmar · Accepted Answer · 2016-01-26 22:08:58Z

You need to use LEFT JOIN, so that the joins will succeed even if there's no matching row in one of the tables.

SELECT * FROM table1 LEFT JOIN table2 ON table2.email = table1.email LEFT JOIN table3 ON table3.email = table2.email WHERE month = 'numberHere' AND (table2.email IS NOT NULL OR table3.email IS NOT NULL) ORDER BY submitdate DESC

The additional conditions filter out rows that have no match in either of the tables.

Collectives™ on Stack Overflow

Joining 3 tables with mySQL with 1 query

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related