CODEWITHSUNDEEP
sql-server
Raj
Raj

Reputation: 343

Distinct values from multiple tables from two different server in sql server

I have multiple tables in two different server. The requirement is to fetch distinct vehicle dealer name from different zones. Ten zones are grouped in single server and other 15 zones in other server. I used UNION ALL since I read it retrieves unique value. But it displayed the dealer name twice since two servers has the same dealer name.

[Server 1] ABC Co (in California zone)

[Server 2] ABC Co (in Newyork zone)

But I want to display only one value as ABC Co despite of its zone. Any help much appreciated?

Upvotes: 1

Views: 38

Answers (1)

tgolisch
tgolisch

Reputation: 6734

Put a DISTINCT (or aggregation) query around your unions

SELECT DISTINCT * FROM 
(
    --your original query goes here
    SELECT * FROM Server1
    UNION ALL
    SELECT * FROM Server2
) A

- or -

SELECT CompanyName, Count(ZoneName) AS RepeatCount
FROM
(
    --your original query goes here
    SELECT CompanyName, ZoneName, etc FROM Server1
    UNION ALL
    SELECT CompanyName, ZoneName, etc FROM Server2
) A
GROUP BY CompanyName

Upvotes: 1

Related Questions