Avatar of JessyRobinson1234
JessyRobinson1234
 asked on

Distinct in union

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 !
Oracle DatabaseMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
Kent Olsen

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Muhammad Ahmad Imran

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Kent Olsen

Hi Jessy,

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
JessyRobinson1234

ASKER
Thank you both, should I go with suggestion 1?
Kent Olsen

I would.  :)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck