CODEWITHSUNDEEP
phpmysql
Christian
Christian

Reputation: 53

MySQL: check occurrences from multiple tables using multiple fields

I have three tables t1, t2 and t2 with the same field names on all three tables such as

  1. id
  2. HomePhone
  3. WorkPhone
  4. MobilePhone

and I want to check for occurrences in t2 and t3 using t1. I came up with MySQL query (below) but I get an empty set when I run it knowing that I do actually have duplicates phone numbers across all three tables:

SELECT t1.id, t1.HomePhone, t1.WorkPhone, t1.MobilePhone, 
(
SELECT COUNT(*) 
FROM t2 
WHERE t1.HomePhone = t2.HomePhone 
    AND 
        (t2.HomePhone IS NOT NULL OR t2.HomePhone != '')
    UNION 
    SELECT COUNT(*) 
    FROM t2 
    WHERE t1.WorkPhone = t2.WorkPhone 
        AND 
            (t2.WorkPhone IS NOT NULL OR t2.WorkPhone != '')
        UNION 
    SELECT COUNT(*)  
    FROM t2 
    WHERE t1.MobilePhone = t2.MobilePhone 
        AND 
            (t2.MobilePhone IS NOT NULL OR MobilePhone != '')
) AS Countt2,
(
SELECT COUNT(*) 
FROM t3 
WHERE t1.HomePhone = t3.HomePhone 
    AND 
        (t3.HomePhone IS NOT NULL OR t3.HomePhone != '')
    UNION 
    SELECT COUNT(*) 
    FROM t3 
    WHERE t1.WorkPhone = t3.WorkPhone 
        AND 
            (t3.WorkPhone IS NOT NULL OR t3.WorkPhone != '')
        UNION 
    SELECT COUNT(*)  
    FROM t3 
    WHERE t1.MobilePhone = t3.MobilePhone 
        AND 
        (t3.MobilePhone IS NOT NULL  OR MobilePhone != '')
) AS Countt3 
FROM t1 
WHERE 
(t1.HomePhone IS NOT NULL OR t1.HomePhone != '') 
AND (t1.WorkPhone IS NOT NULL OR t1.WorkPhone != '')
AND (t1.MobilePhone IS NOT NULL OR t1.MobilePhone != '')

What am I possibly doing wrong here?

Upvotes: 0

Views: 257

Answers (3)

xQbert
xQbert

Reputation: 35343

premise: Duplicate is defined as all 4 fields MATCH. Thus by unioning all tables and "counting them >1 then you'll identify duplicates. and groupConcat lists the tables with dups.

SELECT count(B.src), B.ID, B.HomePhone, B.WorkPHone, B.MobilePHone, 
    group_Concat(B.SRC) as TablesDupIN 
FROM (
(SELECT 't1'  as SRC, id, HomePhone, WorkPhone, MobilePhone FROM T1)
UNION ALL 
(SELECT 'T2', id, HomePhone, WorkPhone, MobilePhone FROM T2)
UNION ALL 
(SELECT 'T3', id, HomePhone, WorkPhone, MobilePhone FROM T3)) B
GROUP BY ID, HomePhone, WorkPhone, MobilePhone
HAVING count(*) > 1

The union all returns all records from each table and doesn't eliminate duplicates. just Union would remove duplicates. t1, t2, t3 into one temporary table. THus we can select from that temporary table grouping by ID, HomePHone, WorkPhone, and MobilePhone. Assuming all 4 fields are needed to determine a duplicate. If this is not the case you can eliminate the field ID if it's just ALL phones that must be the same. By grouping the results we combine like rows for ID, homephone, workphone, and mobilephone. I use the 'T1', 'T2' so as to identify the tables which have duplicates and the duplicate is on the ID, and 3 phone numbers in the tables listed in TalesDupIn.

results would be something like

2 4 555-5555 555-5555 555-5555 (t1,t2)
2 5 666-6666 666-6666 666-6666 (t1,t3)
3 7 777-7777 777-7777 777-7777 (t1,t2,t3)
2 8 888-8888 888-8888 888-8888 (t2,t3)

THis implies ID 4 is found in both table 1 and table 2 ID 5 is found in table 1 and table 3 with.
But the duplicate is determined based on all 4 fields MATCHING ID + 3 phones. IF ID isn't part of the duplicate it can be eliminated from the select and group by, added as a group_Concat(ID) and that combined with the tablesDupIn would tell you the ID and tables involved. Depending on what you intend to do with the results this may be beneficial or not...

Upvotes: 1

Eugen Rieck
Eugen Rieck

Reputation: 65314

SELECT 
  COUNT(DISTINCT t1.id)
  COUNT(DISTINCT t2.id)
  COUNT(DISTINCT t3.id)
FROM t1
LEFT JOIN t2 ON
  (t1.HomePhone=t2.HomePhone AND t1.HomePhone IS nOT NULL)
  OR (t1.WorkPhone=t2.WorkPhone AND t1.WorkPhone IS nOT NULL)
  OR (t1.MobilePhone=t2.MobilePhone AND t1.MobilePhone IS nOT NULL)
LEFT JOIN t3 ON
  (t1.HomePhone=t3.HomePhone AND t1.HomePhone IS nOT NULL)
  OR (t1.WorkPhone=t3.WorkPhone AND t1.WorkPhone IS nOT NULL)
  OR (t1.MobilePhone=t3.MobilePhone AND t1.MobilePhone IS nOT NULL)

Upvotes: 0

echo_Me
echo_Me

Reputation: 37253

you may use OR instead of AND between those

   AND t3.HomePhone IS NOT NULL 
   AND t3.HomePhone != ''

like that

    AND (t3.HomePhone IS NOT NULL 
         OR t3.HomePhone != '')

change with OR in all your query

Upvotes: 0

Related Questions