Re: Many to Many Query...

[Walter McConnell]

Jay,

Thanks so much for your help. I'm off to put your suggestions into action. As I mulled it over last night, trying to figure it out for myself, I thought that some sort of subquery was the answer, I just couldn't figure out exactly what it was. The final query you provided, using COUNT(*) and a HAVING clause is the kind of clever thing I was hoping to have come up with on my own.

I'm working with version 4.0.18 right now, but I'm going to upgrade to the 4.1.12 version today based on the solutions you've provided. I find the subquery based solutions much easier to understand and work with than self-joins (a concept I haven't quite gotten through my head yet). Thanks also for your suggestions regarding the service_map table. I'll implement those changes as well.

I appreciate you taking the time to provide such a detailed answer. I'm self taught in this area and sometimes know just enough to paint myself in to a corner. I knew that what I was trying to do was possible - as it must be quite common - but I just wasn't able to piece it all together.

By the way, if you should read this response, I just realized that I left out half the problem. The data I actually want to retrieve and display is from another table altogether, and these services are just used to limit the records returned. As an example, the base query I'm using in my code is something like "SELECT FirstName, LastName, PhoneNumber, Address FROM MemberProfile" Then there might be some simple comparisons based on fields from the search form, such as "WHERE State='AZ' AND City='Phoenix'". Then I need to filter out records that don't match the services selected.

I can probably figure out how to stitch all this together, but if you have any further suggestions they'd be welcome. I look forward to reading your book, though I may not be quite ready for some of it.

Thanks again.