Need Help with SQL Statement

Databases
1

Both of these SQL statements return results:


SELECT person_id, person_first, person_last
FROM people, people_expertise, expertise2people
WHERE expertise2people.person_lookup_id = people.person_id 
AND expertise2people.expertise_lookup_id = people_expertise.expertise_id 
AND expertise_lookup_id = 5
GROUP BY person_id



SELECT person_id, person_first, person_last
FROM people, people_expertise, expertise2people
WHERE expertise2people.person_lookup_id = people.person_id 
AND expertise2people.expertise_lookup_id = people_expertise.expertise_id 
AND expertise_lookup_id = 6
GROUP BY person_id

How come when I combine the expertise I get no results?

SELECT person_id, person_first, person_last
FROM people, people_expertise, expertise2people
WHERE expertise2people.person_lookup_id = people.person_id 
AND expertise2people.expertise_lookup_id = people_expertise.expertise_id 
AND expertise_lookup_id = 5 AND  expertise_lookup_id = 6
GROUP BY person_id

I know there are people in the DB with both expertise 5 and 6. I want only those people. Thanks!

2

because of this piece of code –

AND expertise_lookup_id = 5 AND  expertise_lookup_id = 6

WHERE conditions are evaluated on a row by row basis

there’s no way that expertise_lookup_id can have a value that is simultaneously equal to two different numbers

what you want is this –

AND expertise_lookup_id = 5 OR expertise_lookup_id = 6

and it would be better to write it like this –

AND expertise_lookup_id IN (5,6)