A complex query 1

[RSfromCO]

I have 3 data tables…

People
Person ID, Lastname, Firstname

Cars (a record for each car color)
CarTypeID, CarColor

Musical_Instruments (a record for each instrument type)
InstrumentID, InstrumentName

I then have 2 relating tables to keep track of what Cars People own and what Instruments People Play

Peoples_Cars
PersonID, CarTypeID

Peoples_Musical_Instruments
PersonID, CarTypeID

Here is my question…..
What is the best SQL statement to get a list of PEOPLE who have a CarTypeID = 1 AND a CarTypeID = 3 (they own 2 cars) AND have an InstrumentID = 4 AND have an InstrumentID = 5 (they play 2 instruments) ??????

 

[DanJR]

Hi there,

The answer can be found by separating the question into two questions - the car bit and the instrument bit, then joining them together with a union query. The car bit and the instrument bit are essentually the same sql statement except different tables. The query makes use of cartesian product to get the "AND" e.g. CarTypeID=1 AND CarTypeID=3.

SELECT P.PersonID, P.lastName, P.FirstName
  FROM Persons_Cars AS C1, Persons_Cars AS C2, People AS P
  WHERE C1.PersonID=C2.PersonID 
     And P.PersonID=C1.PersonID 
     And C1.CarTypeID=1 
     And C2.CarTypeID=3

UNION
SELECT P.PersonID, P.lastName, P.FirstName
  FROM Peoples_Musical_Instruments AS M1,Peoples_Musical_Instruments AS M2, People AS P
  WHERE M1.PersonID=M2.PersonID 
      And P.PersonID=M1.PersonID 
      And M1.InstrumentID=4
      And M2.InstrumentID=5;

It's probably a good idea to make your table names singular (tuples are singular) e.g. PEROPLE, CAR, MUSICAL_INSTRUMENT. At the momment you have both.

Cheers,
Dan

 

[DanJR]

oh, i forgot to mention. You didn't specify whether you wanted to include people that played ONLY 2 instruments or AT LEAST 2 instruments (same for CatType). The query above assumes "AT LEAST".

Cheers,
Dan

 

[RSfromCO]

Thanks for the reply so quickly. I should have specified. I need it to select the people who play every instrument specified in the criteria and have every car specified. They may play additional instruments and have additional cars.

For example, If I want to find people who play the trumpet and the trombone, I want it only to return people who play BOTH. The same holds true for the Cars. So If I select Car=Red AND Car=Blue AND Instrument=Trumpet AND Instrument=Trombone.... I only want it to show me the people who play AT least those instruments (they could play more) and those same people have at least one Red car and at least one blue car (they may have more cars).

Your suggestion sounds like it performs what I am trying to do. I'll give it a try (with my real data... I'm not really tring to keep track of cars and instruments).

Thanks.

 

[DanJR]

actually, the UNION will give:
(Car1 AND Car2) OR (Instrument1 AND Instrument2)

sounds like you want:
(Car1 AND Car2) AND (Instrument1 AND Instrument2)

so try this:

SELECT P.PersonID, P.lastName, P.FirstName
  FROM Peoples_Musical_Instruments AS M1,Peoples_Musical_Instruments AS M2,
             Persons_Cars C1, Persons_Cars C2, People P
  WHERE C1.PersonID=C2.PersonID
     And M1.PersonID=M2.PersonID 
     And C1.PersonID=M1.PersonID  
     And P.PersonID=C1.PersonID 
     And C1.CarTypeID=10 
     And C2.CarTypeID=12
     And M1.InstrumentID=4
     And M2.InstrumentID=5;

of course, the "at least" applies with this query as well.

Cheers,
Dan

 

[PHV]

And what about something like this ?
SELECT DISTINCT P.PersonID, P.Lastname, P.Firstname
FROM (People P
INNER JOIN Peoples_Musical_Instruments M ON P.PersonID=M.PersonID)
INNER JOIN Peoples_Cars C ON P.PersonID=C.PersonID
WHERE C.CarTypeID IN (10,12) AND M.InstrumentID IN (4,5)
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DanJR]

that will give:

(CAR1 OR CAR2) AND (Instrument1 OR Instrument2)

cheers,
dan

 

[PHV]

Oops, you're right Dan :~/
And this ?
SELECT P.PersonID, P.Lastname, P.Firstname
FROM (People P
INNER JOIN Peoples_Musical_Instruments M ON P.PersonID=M.PersonID)
INNER JOIN Peoples_Cars C ON P.PersonID=C.PersonID
WHERE C.CarTypeID IN (10,12) AND M.InstrumentID IN (4,5)
GROUP BY P.PersonID, P.Lastname, P.Firstname
HAVING Count(C.CarTypeID)=2 AND Count(M.InstrumentID)=2
;