I have a crossReference table (crossref_room_subject) with conference room, meeting subject and timeSlot IDs. There are multiple entries per timeSlot. The subjectCode table includes a subjectCodeCategory ID also (only one category per subject but multiple subjects per category). For one query that is working, the user is entering multiple subjectCodes and I need to return the timeSlots where all subjectCodes exist. I am now getting good results (thanks again Sparky) using GROUP BY and HAVING but all the information needed for this query in the one table: crossref_room_subject.
I have one last scenario that uses more variables and I can't get it to work. This time the user enters multiple subjectCodeCategorys. I have to link to the subjectCodes table to crossreference subjectCodeCategory with the subjectCode. I have tried it a bunch of ways, mostly changing the SELECT and/or GROUP BY, this seems to be where I should be able to group those subjectCodeCategories. But it is not working (see below). I am getting returns for each timeSlot that has 3 returns--good except it only counts subjectCode, regardless of subjectCodeCategory. I need to return only those timeSlots where one (or more) of each of the three subjectCodeCategories is represented. I have tried adding subjectCodeCategory to the SELECT and/or GROUP BY clauses but don't get any better results.
Thanks for any help or ideas in advance. Grey
SELECT a.meetingID, a.timeSlot FROM crossref_room_subject a , subjectCodes b WHERE a.subjectID = b.subjectID AND ( b.subjectCodeCategory =8 OR b.subjectCodeCategory =19 OR b.subjectCodeCategory =23 ) GROUP BY a.meetingID, a.timeSlot HAVING COUNT( * ) =3 LIMIT 0 , 30