Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005

    Unanswered: SELECT rows in multiple tables


    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,

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    select person_ID
      from persons
      join categories 
        on person_ID = cat_personsID 
     where cat_catID in ( 3 , 7 )
        by person_ID
    having count(*) = 2 | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2005
    Thank you, thank you, thank you!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts