complex query not using group_concat for early versions of mysql 1

[spidgeon12]

Hi,
I was wondering if any body could please help me. I am trying to create a query using the following tables:

Client
ClientInterests
Interests

The client table has a clientID field etc
The interest table has a interestID field etc
and the ClientInterest table has the folowing fields:

clientInterestID
clientID
interestID

Client Table
(clientID) (Name)
1 bob
2 phil
3 james

Intersts table
(interestsID) (name)
1 football
2 rugby
3 cricket

ClientIntersts table
(clientInterestID) (clientID) (interestsID)
1 1 1
2 1 2
3 1 3
4 2 1

Now i wish to search the table to produce a result of the clients (just the client ID) that have both say interests in football and cricket, using interestID. I have worked how to do football or cricket.

e.g
SELECT DISTINCT client.clientID from client INNER JOIN clientintersts on client.clientID=clientintersts.clientID where clientintersts.interestID=1 or clientintersts.interestListID=3

which produces the result clientIDs 1 and 2.

But can not work out how to serach for say football and cricket. I tried
SELECT DISTINCT client.clientID from client INNER JOIN clientintersts on client.clientID=clientintersts.clientID where clientintersts.interestID=1 and clientintersts.interestListID=3

I know i could use :
SELECT c.clientID, group_concat(ci.interestID) as interests
FROM Clients AS c
JOIN ClientInterests AS ci ON c.clientID = ci.clientID
GROUP BY c.clientID
HAVING find_in_set(1, interests)
AND find_in_set(3, interests)

But sadly i am running a version of mysql that does not allow group_concat.


Any help would be great ,

Thanks in advance

 

[r937]

the trick to this type of query is recognizing that you want to examine some property of the set of client interests, and the way to look at a set of rows is with aggregation, i.e. using GROUP BY

select client.clientID 
  from client 
inner 
  join clientintersts 
    on clientintersts.clientID = client.clientID
 where clientintersts.interestID in ( 1,3 )
group
    by client.clientID
having count(*) = 2

r937.com | rudy.ca