(Hypothetical Example)

I have a database of people and the cars they've driven.

Fred
Ford
Chrysler
Volkswagen
George
Jeep
Fiat
Mercedes
Ford
Volkswagen

I need to build a list of which cars have been driven by all people.
In the above example the list would be Volkswagen and Ford.

Given the two tables:
People and Cars

How do I build the SQL statement?

TIA.
B.

On 2011-07-28 22:40, SpreadTooThin wrote:
Assuming:

create table T (
driver ...,
car ...,
primary key (car, driver)
);

There's basically two ways to approach the problem. Perhaps the most
intuitive one is:

1. What cars have been driven by the number of availible drivers

select car
from T
group by car
having count(1) = (select count(distinct driver) from T)

2. What cars have been driven by all drivers, which cant be expressed
directly in sql. Use the transformation:

forall x: p(x) <=> not exists x: not p(x)

In your example, for what cars does it not exist a driver such that the
driver has not driven the car. I'll leave the sql as an exercise for the
reader :-)

FWIW, I only recogniced one car among the vehicles you mentioned and
that's the Mercedes ;-)


/Lennart

On 7/28/2011 4:40 PM, SpreadTooThin wrote:
Not knowing the structure of the database, this may or may not hit the mark.

Count the number of distinct people. For each type of car, count the
number of records in which the car is listed. The cars driven by all
people will be where count of distinct people == number of records in
which the car is listed.

So above, distinct people == 2; number of records in which Ford occurs
== 2; number of records in which Volkswagen occurs == 2.

Bill B

On 7/28/2011 4:40 PM, SpreadTooThin wrote:
First thing to do would be to normalize your database. You have a
many-to-many connection between people and cars, and should design your
database accordingly.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

jstucklex (AT) attglobal (DOT) net says...
Absolutely. If you had THREE tables (people, cars, cars_people_drive)
along with the appropriate referential integrity checks and uniqueness
constraints then its simple:

select cars.car_name, count(cars_people_drive.person_id)
from cars, cars_people_drive
where cars.car_id = cars_people_drive.car_id
group by cars.car_name
having count(cars_people_drive.person_id) =
(select count(people.person_id)
from people);

GM

On Jul 28, 7:57*pm, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote:

ICBW, but, with such a simple request, it looks more like he is trying
to get someone to do his homework for him....

On 7/29/2011 3:42 PM, onedbguru wrote:
I agree, which is why I gave him a pointer to how to find the correct
solution instead of the solution itself. He's more likely to remember
it the next time if he has to work it out for himself.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Dude.. may days of homework are long over.. but thanks for the jab in
the side.
This is actually for a medical imaging database and determining what
scans have been done on which people. For research purposes only
scans that have been done on the entire cohort are of use to me..
I could tell you more but then I'd have to shoot myself for releasing
private information into the public sector. Get it?

SpreadTooThin wrote:
ah. makes sense..

the answer is still to normalise the database.

The the query gets to be simple.

On 2011-08-02 17:49, SpreadTooThin wrote:

Google for "relational division".


/Lennart