Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2006
    Posts
    1

    Unanswered: Multiple search parameters on same table field

    Hi

    I have two tables with the following structure and data:

    STRUCTURE of table person:
    person_id int (11)
    person_name varchar(255)

    DATA:
    person_id,person_name
    ---------------------
    1,John Doe


    STRUCTURE of table email:
    email_id int(11)
    person_id int(11)
    email_email varchar(255)

    DATA:
    email_id, person_id, email_email
    ------------------------------
    1, 1, john@msn.com
    2, 1, john@hotmail.com


    There is no problem with selecting persons with EITHER john@msn.com or john@hotmail.com.
    I use the following query

    SELECT person_name, email_email
    FROM person as p
    LEFT JOIN email as ON p.person_id=e.person_id
    WHERE email_email='john@msn.com' OR email_email='john@hotmail.com';

    The above produces something like:

    person_name, email_email
    ------------------------
    John Doe, john@msn.com
    John Doe, john@hotmail.com



    BUT what if I want find persons that has BOTH john@msn.com and john@hotmail.com ???????????

    You can´t just switch operator to AND in the WHERE part in the query, because thats returns
    zero rows of course. I been scratching my head for days.

    The mysql server Im working with is PRE 4.1 (older than 4.1) and PHP 4.

    Please help

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select p.person_name
      from person as p
    inner
      join email as e
        on e.person_id = p.person_id
     where e.email_email in ( 'john@msn.com'
                            , 'john@hotmail.com' )
    group
        by p.person_name
    having count(*) = 2
    you do not need e.email_email in the SELECT because you already know which ones he's got
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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