Many Duplicates, caused by a phone number column. Need to cut down duplicates

Question

See query below returning approx 38K rows. When 'phone' join and column are removed, it cuts down to the correct 15.5K rows.

SELECT 
    tc.customer_no
    ,fdn.display_name_short             'name'
    ,tc.cont_amt
    ,tc.ref_no
    ,tc.cont_dt
    ,tc.cont_type
    ,tca.fyear
    ,(ISNULL(street1, 'none') + ' ' + ISNULL(city, 'none') + ' ' + ISNULL(state, 'none') 
        + ', ' + ISNULL(postal_code, 'none'))       'address'
    ,ISNULL(tp.phone, 'none')
    ,ISNULL(te.address, 'none')
FROM T_CONTRIBUTION                         tc
    JOIN FT_CONSTITUENT_DISPLAY_NAME()      fdn
        ON tc.customer_no = fdn.customer_no
    JOIN T_CAMPAIGN                         tca
        ON tc.campaign_no = tca.campaign_no
    LEFT JOIN T_ADDRESS                         ta
        ON tc.customer_no = ta.customer_no AND ta.primary_ind = 'y'
    LEFT JOIN T_EADDRESS                            te
        ON tc.customer_no = te.customer_no AND te.primary_ind = 'y'
    LEFT JOIN T_PHONE                           tp
        ON tc.customer_no = tp.customer_no
WHERE tca.fyear BETWEEN 2018 AND 2022
        AND tc.cont_amt > 0
        AND te.inactive = 'N'
        AND ta.inactive = 'N'

Any advice as to how i can include the phone number column, while eliminating as many duplicates as possible? I don't have to be highly precise with this query, but need to get the row count down as much as possible. The phone table has about 50 different phone types (ex. 1,2,or 22), and the PK is the phone number. The DB has since moved to using only phone type 1 or 2, but i am searching 4 yrs back which is before they switched to only using two different phone types.

I would suggest some simplified sample data and a a [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) — Stu, Dec 11 '21 at 00:17
_include the phone number column_ Let's break this down. **THE** phone number implies you want one phone number for a given customer - correct? So for a customer with multiple phone numbers, which one do you want? As a starting point I suggest you search "first in group" to see common solutions. Note that two of your three left joins are logically converted to inner joins due to the use of columns from those tables in the WHERE clause. — SMor, Dec 11 '21 at 00:26
@SMor Thanks friend. Ended up making a cte, and adding a rowcount with a partioned window function to achieve first in group, in that cte. Fixed the where clause as well, and the query is now functional! — SQLdude2000, Dec 11 '21 at 01:54
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Dec 11 '21 at 21:38

score 0 · Answer 1 · answered Dec 11 '21 at 02:04

0

Followed suggestions in comments, ended up with:

CTE to create numbered and grouped rows

WITH cte AS (
   SELECT customer_no, phone
        , row_number() OVER(PARTITION BY customer_no ORDER BY phone) AS rn
   FROM   T_PHONE
   )

Then referenced said cte in the main query's select.

Finally added

WHERE cte.rn = 1

Which selected the first phone number at random, in each group of customer's phones numbers.

answered Dec 11 '21 at 02:04

SQLdude2000

1
1

1

For only 1 field you could also just get the MAX phone : `LEFT JOIN (select customer_no, MAX(phone) as phone FROM T_PHONE GROUP BY customer_no) as tp` . – LukStorms Dec 11 '21 at 07:30

Many Duplicates, caused by a phone number column. Need to cut down duplicates

1 Answers1