SELECT rows in multiple tables

mg999 · #1 (**permalink**) 12-10-05, 06:21

Hi,

My Web host is still using MySQL 4.0.26.

I have a question where I'd like to create a selection of people in my persons table by searching in a categories table where if I select 2 categories, all the people found must have both selected.

In the categories table, I have a cat_personsID field that is related to the person_ID field.

When I do the following:

SELECT persons,categories WHERE person_ID = cat_personsID AND (cat_catID = 3 OR cat_catID = 7)

I'm able to get all the records with either 3 or 7, but I want to get all the people have both categories selected so that every person has a categories record with 3 and a categories record with 7. I tried with AND, but of course, that won't work...I get NO records at all. And I do have records with both 3 and 7 selected.

I'd be grateful for any help! :-)

Take care,
Mimi.

r937 · #2 (**permalink**) 12-10-05, 06:33

Code:

select person_ID
  from persons
inner
  join categories 
    on person_ID = cat_personsID 
 where cat_catID in ( 3 , 7 )
group
    by person_ID
having count(*) = 2

mg999 · #3 (**permalink**) 12-10-05, 06:51

Thank you, thank you, thank you!!!

Internet Services

Web Development

Digital Marketing

Consumer Tech