Mysql Multiple ANDS

I have three tables persons, jobs, jobs_persons

One person can ha开发者_JS百科ve multiple jobs.

Person 1 is Technical Support AND Manager

Person 2 is Technical Support

Person 3 Is Manager

Job 1 Technical Support

Job 2 Manager

I need to find a query give me the result for the person who currently is Technical Support AND Manager

The answer would be only Person 1

SELECT persons.*
FROM persons INNER JOIN jobs_persons ON persons.id = jobs_persons.person_id
INNER JOIN jobs ON jobs.id = jobs_persons.job_id
WHERE job.id IN (1,2)

Returns 3 rows

SELECT persons.*
FROM persons INNER JOIN jobs_persons ON persons.id = jobs_persons.person_id
INNER JOIN jobs ON jobs.id = jobs_persons.job_id
WHERE job.id = 1 AND job.id = 2

Returns 0 rows.

I'm currently working on Ruby on Rails. Somebody can help?

You want to use an OR operator. Using job.id = 1 AND job.id = 2 will only return elements where id equals 1 and at the same time 2. No element can do that. You want elemets where th id is 1 or where the id is 2.

to make it more obvious:

SELECT * FROM table WHERE lastname = 'Smith' AND firstname = 'James';

when executing this you obviously don't want everybody who is called Smith or James. ;-)

EDIT:

Misread the question. what you need is a second join to join the jobs table two times in and join them with the different jobs. It is a bit hard as you didn't show the schema, but this might work:

SELECT persons.*
FROM persons
INNER JOIN jobs_persons jp1 ON persons.id = jp1.person_id
INNER JOIN jobs_persons jp2 ON persons.id = jp2.person_id
INNER JOIN jobs j1 ON j1.id = jp1.job_id 
INNER JOIN jobs j2 ON j2.id = jp2.job_id
WHERE j1.id = 1 AND j2.id = 2

Try this to get all the person who has got a job of 1 AND 2 in your associative entity table. No need to hit the job table.

SELECT p.*
FROM persons p
WHERE id in (
   SELECT person_id FROM jobs_persons 
   WHERE job_id IN (1,2)
   GROUP BY person_id 
   HAVING COUNT(*) = 2
);

With your recent comments, it seems you're having performance problems. That's really outside of the scope of this question and answer.

You need to make sure your indexes are in place on the appropriate columns:

• jobs_person.job_id
• persons.id