Multiple search parameters on the same table field 1

[spaccaps]

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

 

[r937]

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

r937.com | rudy.ca

 

[spaccaps]

Thanks alot for the great quick reply.

Now to my real problem. I have several tables like email that is connected with person, tables like address, phone, webadress
and so forth. I have customers that stores their contacts(with the described info) in my database and they have a web search
interface were they can search for contacts(persons). They can add several search criterias.

An example of a search can in pseudo code look like:

Select * persons whichs
has an address in London AND IN New York
AND
has phonenr 111222 AND 333444
AND
has email john@msn.com AND john@hotmail.com


I understand how to join several tables in the query but how do I
change the last part of the query "having count(*)=2"

/Pierre

 

[r937]

select *
  from person
 where person_name in
       (
       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
       )
  and person_name in
       (
       select p.person_name
         from person as p
       inner
         join telephones as t
           on t.person_id = p.person_id
        where t.phonenr
           in ( '111222'
              , '333444' )
       group
           by p.person_name
       having count(*) = 2
       )
  and person_name in
       (
       select p.person_name
         from person as p
       inner
         join addresses as a
           on a.person_id = p.person_id
        where a.city
           in ( 'London'
              , 'New York' )
       group
           by p.person_name
       having count(*) = 2
       )

r937.com | rudy.ca

 

[spaccaps]

You are just great, thanks. Now there is just one small problem my mysql version is prior to 4.1 and don´t allow subqueries. Any other suggestions.

 

[r937]

you have two choices

1. upgrade

2. write three separate queries and resolve them
2a. in php using arrays
2b. in mysql using temp tables

and please, do us a favour -- if you ask a question on a public forum and you are several major releases behind, please mention this, as it will save people the time and aggravation of writing a solution for you that you cannot implement anyway!!

r937.com | rudy.ca

 

[spaccaps]

Thanks a lot. If you look at my first post I DID mention what server version I had ----
The mysql server Im working with is PRE 4.1--- (older than 4.1) and PHP 4. But thank you for your time.

 

[r937]

yes, you are right, my sincere apologies

the rant was undeserved, i should have read your post more closely

i'm sorry

r937.com | rudy.ca

 

[spaccaps]

No problem. I have given my great tips. Thanks a alot.

 

[guelphdad]

In Rudy's defence, he is on a lot of discussion forums, the majority of people that do post with older versions do not note that until AFTER the error crops up. I think he was on auto pilot.

 

[r937]

yes, autopilot

i just don't know what happened, i always double-check for a version number before my standard "to save us the trouble" rant

maybe too much football yesterday ...

r937.com | rudy.ca

 

[spaccaps]

My last post didn´t make sense. I meant to say that I have been given great tips. So no offense taken. Thanks for great chat.