Remove duplicate value on one column based on value of other column

I have a table called Table1 see below

PRODUCT CUSTOMER COMP
DICE DAVES PET SHOP Billed
DICE CLAXTONS ToT
CARDS VIEWSONIC NITS
CARDS NORTHERN LIGHTS Billed
CARDS NORTHERN LIGHTS NITS
BOX TABLEAU Billed
BOX TABLEAU ToT


There are some values where there is a duplicate in the CUSTOMER field but it will always contain Billed in at least one of the COMP sections if it is a duplicate, so I want the resulting query to only return the value in COMP where it is Billed for the duplicates so the resulting table would look like

PRODUCT CUSTOMER COMP
DICE DAVES PET SHOP Billed
DICE CLAXTONS ToT
CARDS VIEWSONIC NITS
CARDS NORTHERN LIGHTS Billed
BOX TABLEAU Billed


Here is the SQL I tried

SELECT *
FROM Table1
WHERE COMP = 'Billed'
UNION ALL
SELECT Table1_A.PRODUCT, Table1_A.CUSTOMER, Table1_A.COMP
FROM Table1 Table1_A
LEFT JOIN (
SELECT *
FROM Table1
WHERE COMP != 'Billed'
) Table1_B ON Table1_B.PRODUCT = Table1_A.PRODUCT
AND Table1_B.CUSTOMER = Table1_A.CUSTOMER


I thought if I put in a UNION and referenced both equal to Billed and not equal to Billed, then it would keep the format I'm looking for but it just repeated every single CUSTOMER for every single COMP

Continue reading...