I am trying to check if there are matching emails in multiple tables, and if there are, then display the status of the emails per table.
Here is the query I’ve been using to check 3 tables:
SELECT
rr.`email`
, rr.`status`
, pp.`status`
, bb.`status`
FROM
redtable rr
, purpletable pp
, bluetable bb
WHERE (rr.`email` = pp.`email` = bb.`email`)
This seems to work fine. I get back records that resemble this:
email | status_rr | status_pp | status_bb
--------------------------------------------
f@gmail | ACTIVE | ACTIVE | UNSUB
---------------------------------------------
e@gmail | NONACTIVE | ACTIVE | ACTIVE
---------------------------------------------
P@yahoo | ACTIVE | UNSUB | UNSUB
--------------------------------------------
O@msn | ACTIVE | ACTIVE | ACTIVE
--------------------------------------------
But when I add a fourth table, things start to get wonky and look like this:
email | status_rr | status_pp | status_bb | status_cc
-------------------------------------------------------
P@yahoo | ACTIVE | ACTIVE | ACTIVE | ACTIVE
-------------------------------------------------------
P@yahoo | ACTIVE | ACTIVE | ACTIVE | UNSUB
-------------------------------------------------------
P@yahoo | ACTIVE | ACTIVE | ACTIVE | OPT OUT
-------------------------------------------------------
P@yahoo | ACTIVE | ACTIVE | ACTIVE | ACTIVE
-------------------------------------------------------
If you’ll notice, when I add the 4th table, it starts duplicating the first column. I have to scroll down a ways until I see a different set of emails but they all seem to be duplicating.
So all though the query is technically working, it’s not.
I need to find matching emails in each table, and display their status.
- Note – the emails in each table are unique.
What am I doing wrong and how can I make this work?
2
If you want to get emails that exists in all the tables and you are certain that email is a unique key in each table, you can just use the straightforward way to define joins. This will help avoiding confusions.
SELECT
rr.email
, rr.status
, pp.status
, bb.status
, cc.status
FROM redtable rr
JOIN purpletable pp ON pp.email = rr.email
JOIN bluetable bb ON bb.email = rr.email
JOIN calypsotable cc ON cc.email = rr.email