SQL Query - how do I do this?

  • Thread starter Thread starter Sparky
  • Start date Start date
S

Sparky

I have a Person table, with one entry per person. I have
a Car table with one entry per car. I have a PersonCar
table, one entry per person per car owned.

If I wanted to return a list of all people who had a Ford
AND a VW... how can I do that?

In effect, I can drop the Car table from the query, and
do something like:

SELECT tblPerson.*
FROM tblPerson
INNER JOIN
PersonCar ON
Person.[id] = PersonCar.PersonID

But the Where is an issue - if I do a Where
PersonCar.CarID="VW" AND PersonCar.CarID="Ford",
obviously no Person entries will be returned, but if I do
an OR, I'll get people who have either, but not
exclusivly those who only have both.

Seems like an easy one but it's got me stumped! Any help
much appreciated!

Sparky
 
There's probably a more elegant way of doing this but here goes:

select tblPerson.* from tblPerson
join
(SELECT personID from personCar
join car
on personCar.carID = car.carID
and car.carname in ('ford', 'vw')
group by personCar.personid
having count(*) = (select count(*) from car where carname in ('ford',
'vw'))) FordAndVW
on person.personid = FordAndVW.personid

hope this helps.

Sal
 
Hi Miha

Thanks for the reply. That will still return results for
people who have ANY of the cars included in the search,
rather than only ones that have ALL the cars.

Sparky
-----Original Message-----
Hi,

SELECT DISTINCT tblPerson...
and do an OR.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com

I have a Person table, with one entry per person. I have
a Car table with one entry per car. I have a PersonCar
table, one entry per person per car owned.

If I wanted to return a list of all people who had a Ford
AND a VW... how can I do that?

In effect, I can drop the Car table from the query, and
do something like:

SELECT tblPerson.*
FROM tblPerson
INNER JOIN
PersonCar ON
Person.[id] = PersonCar.PersonID

But the Where is an issue - if I do a Where
PersonCar.CarID="VW" AND PersonCar.CarID="Ford",
obviously no Person entries will be returned, but if I do
an OR, I'll get people who have either, but not
exclusivly those who only have both.

Seems like an easy one but it's got me stumped! Any help
much appreciated!

Sparky
Click to expand...


.
Click to expand...
 
Thanks Sal - haven't tried it but it looks a good
solution! Without subqueries would be nice if anyone else
can help.

Cheers again

Sparky

-----Original Message-----
There's probably a more elegant way of doing this but here goes:

select tblPerson.* from tblPerson
join
(SELECT personID from personCar
join car
on personCar.carID = car.carID
and car.carname in ('ford', 'vw')
group by personCar.personid
having count(*) = (select count(*) from car where carname in ('ford',
'vw'))) FordAndVW
on person.personid = FordAndVW.personid

hope this helps.

Sal

I have a Person table, with one entry per person. I have
a Car table with one entry per car. I have a PersonCar
table, one entry per person per car owned.

If I wanted to return a list of all people who had a Ford
AND a VW... how can I do that?

In effect, I can drop the Car table from the query, and
do something like:

SELECT tblPerson.*
FROM tblPerson
INNER JOIN
PersonCar ON
Person.[id] = PersonCar.PersonID

But the Where is an issue - if I do a Where
PersonCar.CarID="VW" AND PersonCar.CarID="Ford",
obviously no Person entries will be returned, but if I do
an OR, I'll get people who have either, but not
exclusivly those who only have both.

Seems like an easy one but it's got me stumped! Any help
much appreciated!

Sparky
Click to expand...


.
Click to expand...
 
If I understood you correctly I think you want the
intersection of persons who owned ford and vw.

select pc.pid, p.[name]
from personcar pc
join person p
on p.pid = pc.pid
where mftr = 'ford'
and pc.pid in
(select pid from personcar where mftr = 'vw')

That should work.

HTH,
Suresh.
 
Untested:

SELECT person.* FROM tblperson person
INNER JOIN (SELECT PersonID FROM tblPersonCar pc INNER JOIN tblCar c ON
pc.CarID = c.CarID WHERE c.Name = "ford") as FordOwners ON person.PersonID =
FordOwners.PersonID
INNER JOIN (SELECT PersonID FROM tblPersonCar pc INNER JOIN tblCar c ON
pc.CarID = c.CarID WHERE c.Name = "VW") as VWOwners ON person.PersonID =
VWOwners.PersonID
 
Back
Top