I recently found a college course page with a SQL exercise for DIVISION, i.e. find which members of a table have existing matchings with another table in an intersection table. Something like "find the customers who have ordered every part in the catalogue".
What I'm looking for is corroboration/commentary for a second means of solving this problem (mine) that I believe is more intuitive and efficient than that offered as the solution on the course page.
For HOTEL / GUEST / BOOKING with obvious join fields/relations, find which hotels have had every possible guest from Sydney stay there. (*The ill-named 'GUEST' table has members that have not yet been a guest at any hotel.)
[tt]
select h.name
from hotel h
where not exists(select * from guest g where g.address = 'Sydney' and not exists (select * from booking b where b.guest_no = g.guest_no and b.hotel_no = h.hotel_no));
[/tt]
Innermost subquery gets a correlated value from the container query for the hotel_no and a correlated value from the second level subquery for a Sydney guest_no and gets the answer to EXISTS a booking for this person, for the hotel.
The second level subquery looks for a guest from Sidney that does not have EXISTENCE of a booking for the current hotel.
The container query looks for a hotel for which the second level subquery returns FALSE for the EXISTS test.
Generalized rules for division:
Get the dividend name for the member(s) of the set of potential dividends that does not test true for (correlated search for existence of any divisor member that doesn’t test true for (correlated search for existence of divisor/dividend intersection for the current potential dividend member)).
Alternate approach (that should be more efficient):
[tt]
select h.name, count(sb.guest_no)
from hotel h,
(select distinct g.guest_no,b.hotel_no from guest g, booking b
where b.guest_no = g.guest_no
and upper(g.address) = 'SYDNEY') sb --sidney bookings
where h.hotel_no = sb.hotel_no
group by h.name
having count(sb.guest_no) = (select count(*) from guest g where upper(g.address)='SYDNEY')
order by 1
/
[/tt]
I found the kernel of this approach in a paper listed here:
but the lack of DISTINCT usage on the intersection table makes it possible that redundant records for a given record in the divisor table (guest in this case) would satisfy the criterion by presenting a count of intersection pairings that matches the count of possible guests without actually having each possible guest stay there.
Jeff Roberts
Insight Data Consulting
Access and SQL Server Development
What I'm looking for is corroboration/commentary for a second means of solving this problem (mine) that I believe is more intuitive and efficient than that offered as the solution on the course page.
For HOTEL / GUEST / BOOKING with obvious join fields/relations, find which hotels have had every possible guest from Sydney stay there. (*The ill-named 'GUEST' table has members that have not yet been a guest at any hotel.)
[tt]
select h.name
from hotel h
where not exists(select * from guest g where g.address = 'Sydney' and not exists (select * from booking b where b.guest_no = g.guest_no and b.hotel_no = h.hotel_no));
[/tt]
Innermost subquery gets a correlated value from the container query for the hotel_no and a correlated value from the second level subquery for a Sydney guest_no and gets the answer to EXISTS a booking for this person, for the hotel.
The second level subquery looks for a guest from Sidney that does not have EXISTENCE of a booking for the current hotel.
The container query looks for a hotel for which the second level subquery returns FALSE for the EXISTS test.
Generalized rules for division:
Get the dividend name for the member(s) of the set of potential dividends that does not test true for (correlated search for existence of any divisor member that doesn’t test true for (correlated search for existence of divisor/dividend intersection for the current potential dividend member)).
Alternate approach (that should be more efficient):
[tt]
select h.name, count(sb.guest_no)
from hotel h,
(select distinct g.guest_no,b.hotel_no from guest g, booking b
where b.guest_no = g.guest_no
and upper(g.address) = 'SYDNEY') sb --sidney bookings
where h.hotel_no = sb.hotel_no
group by h.name
having count(sb.guest_no) = (select count(*) from guest g where upper(g.address)='SYDNEY')
order by 1
/
[/tt]
I found the kernel of this approach in a paper listed here:
but the lack of DISTINCT usage on the intersection table makes it possible that redundant records for a given record in the divisor table (guest in this case) would satisfy the criterion by presenting a count of intersection pairings that matches the count of possible guests without actually having each possible guest stay there.
Jeff Roberts
Insight Data Consulting
Access and SQL Server Development