Extract data from 2 tables based on matching record

Question

I am trying to get data from 2 different tables that have the exact same record in MySQL. Here is example data

TABLE 1
----------------------------------
ID          NAME            MEMBER          SMS_MEMBER
1           JOHN            YES             NO
2           JOY             NO              NO
3           SMITH           NO              YES
4           ANDRES          YES             YES


TABLE 2
----------------------------------
ID          NAME            MEMBER          SMS_MEMBER
1           JOHN            YES             NO
2           JOY             NO              YES
3           SMITH           NO              YES
4           ANDRES          YES             YES

Here is my logical query

SELECT * FROM TABLE1, TABLE2 
WHERE 
TABLE1.ID = TABLE2.ID 
AND 
TABLE1.NAME = TABLE2.NAME 
AND
TABLE1.EMAIL_MEMBER = TABLE2.EMAIL_MEMBER
AND
TABLE1.SMS_MEMBER = TABLE2.SMS_MEMBER;

The expected result should be

----------------------------------
ID          NAME            MEMBER          SMS_MEMBER
1           JOHN            YES             NO
3           SMITH           NO              YES
4           ANDRES          YES             YES

Since this below record has a different value on SMS_MEMBER field so it will be excluded

EXCLUDED RECORD
----------------------------------
ID          NAME            MEMBER          SMS_MEMBER
2           JOY             NO              YES

Would be great if someone suggests working query on it?

Just do `SELECT Table2.*`. Also, Please don't use Old comma based Implicit joins and use Modern [Explicit `Join` based syntax](https://stackoverflow.com/q/5654278/2469308) — Madhur Bhaiya, Jul 01 '19 at 12:40

Tim Biegeleisen · Accepted Answer · 2019-07-01T12:58:22.337

2

Your current query should actually be working, though I would write it using an explicit inner join:

SELECT DISTINCT t1.*
FROM TABLE1 t1
INNER JOIN TABLE2 t2
    ON t1.ID = t2.ID AND
       t1.NAME = t2.NAME AND
       t1.MEMBER = t2.MEMBER AND
       t1.SMS_MEMBER = t2.SMS_MEMBER;

Just for fun, here is another way to do your query, using a union followed by an aggregation:

SELECT ID, NAME, MEMBER, SMS_MEMBER
FROM
(
    SELECT ID, NAME, MEMBER, SMS_MEMBER FROM TABLE1
    UNION ALL
    SELECT ID, NAME, MEMBER, SMS_MEMBER FROM TABLE2
) t
GROUP BY ID, NAME, MEMBER, SMS_MEMBER
HAVING COUNT(*) = 2;

The condition HAVING COUNT(*) = 2 ensures that only identical records which appeared in both tables end up in the result set.

edited Jul 01 '19 at 12:58

answered Jul 01 '19 at 12:40

Tim Biegeleisen

502,043
27
286
360

1

Seams to be missing a `DISTINCT` on the first query ? I assume this is meant to simulate `INTERSECT` ? ANSI/ISO SQL standards INTERSECT returns distinct records right? – Raymond Nijland Jul 01 '19 at 12:53
1

@RaymondNijland Good eye. – Tim Biegeleisen Jul 01 '19 at 12:58
It works perfect, just last thing, how to do inverse query? i mean those records that do not match ? – Mr.Devops Jul 01 '19 at 13:07
*"It works perfect, just last thing, how to do inverse query? i mean those records that do not match ? "* @SyedRazaAli use `LEFT JOIN ... WHERE t2.ID IS NULL` in the first, change `HAVING COUNT(*) <> 2` or this one might be better ``HAVING COUNT(*) = 1`` in the second.. – Raymond Nijland Jul 01 '19 at 13:18
@RaymondNijland can you write query properly as answer, please – Mr.Devops Jul 01 '19 at 13:27
1

@TimBiegeleisen . . . The `COUNT(*)` only guarantees that a record occurs twice, not that they are in each table. – Gordon Linoff Jul 01 '19 at 13:49
1

@GordonLinoff I was assuming that duplicates would never occur within a single table. Maybe I should have stated this. – Tim Biegeleisen Jul 01 '19 at 13:57

score 0 · Answer 2 · answered Jul 01 '19 at 12:41

0

What you want is intersect, but MySQL doesn't support it.

For a complete overlap, your code should basically work, but I would recommend:

SELECT t1.*
FROM TABLE1 t1 JOIN
     TABLE2 t2
     USING (ID, NAME, EMAIL_MEMBER, SMS_MEMBER);

answered Jul 01 '19 at 12:41

Gordon Linoff

1,242,037
58
646
786

Missing `DISTINCT` ? ANSI/ISO SQL standards `INTERSECT` returns distinct records right? – Raymond Nijland Jul 01 '19 at 12:56

Extract data from 2 tables based on matching record

2 Answers2