Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Check to see if a value exists in multiple tables and display an associating value

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?

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0 
    The query you provided uses a multi-table query (probably to use implicit joins) and attempts to set the email columns of the three tables equal using the = operator in the WHERE clause. However, this is incorrect because the expression rr.email = pp.email = bb.email is invalid in SQL.

The correct way to write it is to use AND or JOIN to connect these tables. Here is the correct way to use JOIN:
SELECT 
  rr.email,
  rr.status AS rr_status,
  pp.status AS pp_status,
  bb.status AS bb_status
FROM redtable rr
JOIN purpletable pp ON rr.email = pp.email
JOIN bluetable bb ON rr.email = bb.email;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search