Hi,
I have the following union query:
SELECT
'BECKWITH' as 'SLC',
SOI,
Cast(RTrim(substring(supplier_description,patindex('%-%',supplier_description)+1,50)) as nvarchar(50)) 'Supplier'
FROM CEVA5_Compass.dbo.titems_inv with(nolock)
JOIN CEVA5_Compass.dbo.tsupplier on CEVA5_Compass.dbo.titems_inv.supplier_key = CEVA5_Compass.dbo.tsupplier.supplier_key
GROUP BY SOI, supplier_description
UNION ALL
SELECT
'BECKWITH' as 'SLC',
SOI,
Cast(RTrim(substring(supplier_description,patindex('%-%',supplier_description)+1,50)) as nvarchar(50)) 'Supplier'
FROM NFC_Compass.dbo.titems_inv with(nolock)
JOIN NFC_Compass.dbo.tsupplier on NFC_Compass.dbo.titems_inv.supplier_key = NFC_Compass.dbo.tsupplier.supplier_key
GROUP BY SOI, supplier_description
Which yields (subset)
BECKWITH 69640 AVOCENT
BECKWITH 13560 BROCADE
BECKWITH 70020 CERQA
BECKWITH 56730 CHICONY AMERICA, INC
BECKWITH 56730 CHICONY AMERICA, INC
BECKWITH 75160 EATON CORPORATION
BECKWITH 69070 ELPIDA
BECKWITH GIG01 EMULEX
BECKWITH 72743 FORCECON
BECKWITH 72744 FORCECON
My question is how do I only get distinct values meaning that it is ok to display FORCECON twice since the second column is different (72743 abd 72744) but I only want to display CHICONY AMERICA, INC once since both rows are identical.
Thank you !
Leo's code is correct. The explanation is that UNION ALL does just that. It performs a UNION of ALL rows in the result set. The UNION operator filters out the duplicates.
Good Luck,
Kent