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

    Unanswered: Multiple search parameters on same table field


    I have two tables with the following structure and data:

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

    1,John Doe

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

    email_id, person_id, email_email
    1, 1,
    2, 1,

    There is no problem with selecting persons with EITHER or
    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='' OR email_email='';

    The above produces something like:

    person_name, email_email
    John Doe,
    John Doe,

    BUT what if I want find persons that has BOTH and ???????????

    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
    Toronto, Canada
    select p.person_name
      from person as p
      join email as e
        on e.person_id = p.person_id
     where e.email_email in ( ''
                            , '' )
        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 | @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