CODEWITHSUNDEEP
sqlsql-server
shinra tensei
shinra tensei

Reputation: 713

SQL Server : add data field from another table on subquery

I have a query that shows duplicate rows in a table. Now I'm stuck on how to add data fields from another table into my query.

SELECT  
    T1.lname, T1.fname, T1.rtg_id
FROM 
    (SELECT 
         lname, fname, rtg_id
     FROM 
         cpmd b
     GROUP BY 
         lname, fname, rtg_id
     HAVING 
         COUNT(*) >= 2) T1
JOIN 
    cpmd T2 ON T1.lname = T2.lname 
            AND T1.fname = T2.fname
            AND T1.rtg_id= T2.rtg_id
ORDER BY 
    lname, fname

This results in below result: (I removed the id in my actual query, just showed here for you)

md_id   lname   fname   rtg_id
------------------------------
9901    Smith   John    12300   
9902    Smith   John    12300   
9903    Lee     Bob     45600
9904    Lee     Bob     45600

I need to get the phone number and fax number of each person. Some records, although the same name, etc may have different phone and fax numbers. SO I need something like this:

md_id   lname   fname   rtg_id   Home            Fax
-------------------------------------------------------------
9901    Smith   John    12300   (654) 5894676   (231) 7684353
9902    Smith   John    12300   (546) 4576369    NULL   
9903    Lee     Bob     45600   (654) 9844576   (234) 2835657
9904    Lee     Bob     45600   (778) 6780853   NULL

These are the two tables that contain phone type and phone number.

cpmd_phone table:

md_id   phone_id    phone_type
------------------------------
9901    90          FAX 
9901    91          HOME
9902    92          FAX
9902    93          HOME
9903    94          FAX 
9903    95          HOME
9904    96          FAX
9904    97          HOME

csphone table

phone_id    area_code   phone_no
---------------------------------
90          231         7684353 
91          654         5894676
92          null        null    
93          546         4576369
94          234         2835657 
95          654         9844576
96          null        null
97          778         6780853

This is what I have so far but it doesn't work correctly. Sometimes it shows the 2 duplicates, sometimes it just show 1 record..

SELECT  
    T1.lname, T1.fname, T1.asmdid, T1.rtg_id, t1.Phone, T1.Fax   
FROM 
    (SELECT  
         lname, fname, rtg_id, 
         MIN(b.md_id) AS asmdid,
         MIN(CASE WHEN (d.phone_type_cn = 2) 
                     THEN '(' + e.area_code + ') ' + e.phone_no 
                     ELSE NULL 
             END) AS Phone,
         MIN(CASE WHEN (d.phone_type_cn = 4) 
                     THEN '(' + e.area_code + ') ' + e.phone_no 
                     ELSE NULL 
             END) AS Fax
     FROM 
         cpmd b
     JOIN 
         cpmd_phone d on b.md_id = d.md_id
     JOIN 
         csphone e ON d.phone_id = e.phone_id
     GROUP BY 
         lname, fname, rtg_id
    HAVING 
         COUNT(*) >= 2) T1
JOIN 
    cpmd T2 ON T1.lname = T2.lname 
            AND T1.fname = T2.fname
            AND T1.rtg_id = T2.rtg_id
            AND T1.asmdid <> T2.md_id
ORDER BY 
    lname, fname

Upvotes: 0

Views: 57

Answers (1)

S3S
S3S

Reputation: 25152

Sounds like a case statement and a couple of joins...

SELECT  
    T1.lname, 
    T1.fname, 
    T1.rtg_id,
    Home = max(case when ph.phone_type = 'HOME' then '(' + cast(cs.area_code as char(3)) + ') ' + cast(cs.phone_no as char(7)) end),
    Fax = max(case when ph.phone_type = 'FAX' then '(' + cast(cs.area_code as char(3)) + ') ' + cast(cs.phone_no as char(7)) end)
FROM 
    (SELECT lname, fname, rtg_id
    FROM cpmd b
    GROUP BY lname, fname, rtg_id
    HAVING COUNT(*) >= 2) T1
JOIN cpmd T2 ON 
    T1.lname = T2.lname 
    AND T1.fname = T2.fname
    AND T1.rtg_id= T2.rtg_id
LEFT JOIN  cpmd_phone ph ON
    PH.md_id = t2.md_id
LEFT JOIN csphone cs ON
    cs.phone_id = ph.phone_id
GROUP BY
    T1.lname, 
    T1.fname, 
    T1.rtg_id,
ORDER BY lname, fname

Upvotes: 1

Related Questions