SQL division

Question

I have three tables; Apartment (apartmentnr, floor, apartmenttype), Floor (floornr, house), House(housenr, adress)

Now, I want to show housenr and adresses of houses that have apartments of all apartmenttypes (1-4), using 'NOT EXISTS'.

Please help me with suggestions on how to do this!

Answer 1

Ah then you want to use Chris Dates approach, rather than the one made popular Joe Celko? If you are not sure, both approaches are discussed here:

Relational Division by Joe Celko

Answer 2

If I understand your relationships properly you could something like this. Remember you could use alias's for the tables to make it slightly easier to read, I've just kept them in to make it clear what I'm doing.

Select  House.hoursenr, address
inner join Floor on House.housenr = Floor.house
inner join Apartment on Apartment.floor = Floor.floornr
where Apartment.apartmenttype in (1,2,3,4)

I would argue that some of your entity relationships are the wrong way round in your table definitions.

Answer 3

EDIT: Sorry, misunderstood your question.

Answer 4

Given a structure like this:

Houses

ID Address

Floors

ID HouseID

Apartments

Id FloorId ApartmentType

I think this will do the trick:

SELECT H.ID FROM Houses H JOIN Floors F ON H.Id = F.HouseId JOIN Apartments A on F.Id = A.FloorId AND A.ApartmentType = 1 AND H.Id IN 
(SELECT H.ID FROM Houses H JOIN Floors F ON H.Id = F.HouseId JOIN Apartments A on F.Id = A.FloorId AND A.ApartmentType = 2) AND H.Id IN 
(SELECT H.ID FROM Houses H JOIN Floors F ON H.Id = F.HouseId JOIN Apartments A on F.Id = A.FloorId AND A.ApartmentType = 3) AND H.Id IN 
(SELECT H.ID FROM Houses H JOIN Floors F ON H.Id = F.HouseId JOIN Apartments A on F.Id = A.FloorId AND A.ApartmentType = 4)

In English, it means, give me the set of all houses with an apartment of type 1 that are also in the set of houses with an apartment of type 2...and so on.

Answer 5

As @onedaywhen hints, not exists is pretty ponderous for this task, and count distinct offers leaner syntax (performance issues are mentioned in the article he points to):

SELECT House.adress
FROM House
JOIN Floor ON (House.housenr=Floor.house)
JOIN Apartment ON (Floor.floornr=Apartment.floor)
GROUP BY House.housenr
HAVING COUNT(DISTINCT Apartment.apartmenttype)=4

which essentially says "show adress [[sic]] of houses with 4 different types of apartments". Only good reason to force the use of not exists would be, as others hinted, homework...