MySQL in () query

Mister

I have a user table and a lookup table the lookup table has four colums identified by memberID,activityID, groupID,housingID I am trying to get email addresses from the members table where multiple activityID, groupID and housingID match but i can see this query is trying to do exact matches with the IN statements but I want it to do a broad match so that it fetches all true activityID's, groupID's and housingID's

SELECT DISTINCT(m_email)
FROM tbl_members, tbl_members_lookup
WHERE tbl_members_lookup.activityID IN (8,1)
AND tbl_members_lookup.groupID IN (2,10,3)
AND tbl_members_lookup.housingID IN (2,3)

does this make sense.... 😕

tomhath

Sorry, but I can't figure out what you are trying to do 😕

First, your query doesn't join the two tables, so an email it returns from tbl_members might not be for that member's row from tbl_members_lookup. You need to join on the memberID (see below).

Second, what do you mean by "multiple activityID, groupID and housingID match"? A row in tbl_members_lookup will only have a single set of values, won't it?

SELECT DISTINCT(m_email)
FROM tbl_members, tbl_members_lookup
WHERE  tbl_members.memberID =  tbl_members_lookup.memberID
AND tbl_members_lookup.activityID IN (8,1)
AND tbl_members_lookup.groupID IN (2,10,3)
AND tbl_members_lookup.housingID IN (2,3)

Mister

Sorry yeah I missed that out if the memberID join what I'm trying to do is get all emails where the chosed values for activityID,groupID and housingID are true so that even if the activityID is not 8 or 1 it will still return emails if the groupID is 2 or 3 or 10 or housingID is 2 or 3...

SELECT DISTINCT(m_email)
FROM tbl_members, tbl_members_lookup
WHERE tbl_members.memberID = tbl_members_lookup.memberID
AND tbl_members_lookup.activityID IN (8,1)
OR tbl_members_lookup.groupID IN (2,10,3)
OR tbl_members_lookup.housingID IN (2,3)

Still can't get it to work, I probably I can't see the wood for the trees.

tomhath

Maybe all you need is some parens:

SELECT DISTINCT(m_email)
FROM tbl_members, tbl_members_lookup
WHERE tbl_members.memberID = tbl_members_lookup.memberID
AND
( tbl_members_lookup.activityID IN (8,1)
OR tbl_members_lookup.groupID IN (2,10,3)
OR tbl_members_lookup.housingID IN (2,3)
)

Mister

Sorted cheers!