Query based on count

[brokengod]

Hey guys,

I am trying to design a query based on the following tables:

Table: Person Table: Travel
-------------- --------------
personID ---------| tripID
givenName | tripdate
familyname key port
address | flight
dob |---->personID
direction

I basically want to get result of:

personID, givenName, familyName, address, dob, tripdate, port, flight, direction:

Where the query only returns values where more than one person in the first table travelled on a given trip. ie I want to exclude trips where only one person in the db travelled on the flight.

I have tried starting with a count query as a basis, but have not had much luck.

Appreciate any advice,

BG

 

[PHV]

I guess that the PrimaryKey of Travel is a composite index on (tripID, personID), i.e. that a given trip is tripID:
SELECT P.personID, P.givenName, P.familyName, P.address, P.dob, T.tripdate, T.port, T.flight, T.direction
FROM (Person AS P INNER JOIN Travel AS T ON P.personID = T.personID)
INNER JOIN (SELECT tripID FROM Travel GROUP BY tripID HAVING Count(personID)>1
) AS C ON T.tripID=C.TripID;

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