CODEWITHSUNDEEP
sql-server-2005
user278618
user278618

Reputation: 20262

Group by to find difference

I have table table 

Car:

Id
Name
ProductionCountry

Containers :

ID (GUID)
CarId

I want to get containers, where are cars from different countries

So:

Cars:
1 BMW X5 DE
2 BMW X3 DE
3 AUDI A6 MX
4 AUDI A1 FR
5 VOLVO S40 FR

Containers
Guid1 1
Guid1 2
Guid1 3
Guid2 4
Guid2 5

So in result I get Guid1 because in this container are cars from different countries.

How to write query to get it?

Upvotes: 0

Views: 54

Answers (2)

Preet Sangha
Preet Sangha

Reputation: 65516

I think (I've no access to sql at the moment but I'm guessing at something like)

select id, count(productioncountry)
from (
       select distinct id, productioncountry 
       from container c inner join car on car.id = c.CarId) conts
group by id
having count(productioncountry) > 1

Upvotes: 1

Damian Leszczyński - Vash
Damian Leszczyński - Vash

Reputation: 30875

Question:

How to retrieve container which contain cars from different countries ?

Answer:

SELECT Containers.ID FROM Containers INNER JOIN Car ON Containers.CarId = Car.Id
GROUP BY Containers.ID
HAVING COUNT(DISTINCT ProductionCountry) > 1
GO

How it works ?

We join the tables cars on containers on the car unique identifier. This give us information about what countries identifier are in which containers

Guid1 DE
Guid1 DE
Guid1 MX
Guid2 FR
Guid2 FR

So we group the result by container id and count unique countries ids

Guid1 2
Guid2 1

On the last step we filter the result to have only those containers where the amount of countries is greater than one. 

Guid1

Upvotes: 1

Related Questions