2

I've got a requirement to select data from a table which exists in several databases which hinges on excluding duplicates in a specific way. The data is such that rows in databases B and C might contain the same Account Segment (SGMNTID) as rows in database A. When that is true we want to select the row from the B or C database and exclude the row from the A database.

So I believe this is essentially a selection of everything from B and C and a selection of only unique rows from A when compared to B and C. 

A.DBO.GL40200 
-----
SGMNTID DSCRIPTN
10      ABN Tech
20      ABN Financial
40      Quo USA
41      Quo AUS
62      PO Shanghai

B.DBO.GL40200
-----
SGMNTID DSCRIPTN
40      Quo USA
41      Quo Pt Ltd
60      PO Singapore

C.DBO.GL40200
-----
SGMNTID DSCRIPTN
62      PO Shanghai
63      PO Purchasing

Desired result set would be:

10      ABN Tech
20      ABN Financial
40      Quo USA
41      Quo Pt Ltd
60      PO Singapore
62      PO Shanghai
63      PO Purchasing
Jacob H
  • 2,455
  • 1
  • 12
  • 29
CodenameCain
  • 573
  • 1
  • 9
  • 22
  • A different table or database? Are you using multi companies in GP? – Jacob H Jul 31 '19 at 16:00
  • Correct on using multiple companies in GP. So this is the same table in different company databases. – CodenameCain Jul 31 '19 at 16:01
  • Only 3 companies, or is it dynamic? Maybe write a subquery with a UNION, and then filter out on the outer query? If the number of companies is unknown, you may need dynamic SQL. – Jacob H Jul 31 '19 at 16:03
  • Are there any difference between the rows on A and B with same ID? Right now they look the same so an union of all 3 would give you the desire result. – Juan Carlos Oropeza Jul 31 '19 at 16:09
  • They are not the same. The descriptions vary slightly in some cases. I am coming to the situation where someone already tried with a basic union and that does not work. – CodenameCain Jul 31 '19 at 16:19

2 Answers2

2

Start with a query like:

with q as
(

    select 'A' company, * 
    from A.DBO.GL40200
    UNION ALL
    select 'B' company, * 
    from B.DBO.GL40200
    UNION ALL
    select 'C' company, * 
    from C.DBO.GL40200

), q2 as
(
  select *, row_number() over (partition by SGMNTID, order by company desc) rn
  from q
)
select SGMNTID, DSCRIPTN
from q2 
where rn = 1
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
1
 SELECT * FROM B
 UNION 
 SELECT * FROM C
 UNION 
 SELECT * FROM A WHERE SGMNTID NOT IN (SELECT SGMNTID FROM B UNION SELECT SGMNTID FROM C)

But looking to your data a simple union would also work because UNION remove duplicates.

 SELECT * FROM A
 UNION 
 SELECT * FROM B
 UNION 
 SELECT * FROM C
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118