How do you select multiple records using AND on the left side of a JOIN

Question

I have a simple requirement, but can't get the SELECT statement right, so that it will deliver the desired result.

I have two tables: -

1. Person table with ID and other miscellaneous info
2. A Licences table with the ID to JOIN on and multiple records per person (10-15)

My aim is to get all people with a Blue licence and a White licence (for example).

Using:

SELECT distinctrow person.ID AS client_ID, postcode, locality 
FROM (person INNER JOIN licences ON person.ID = licences.client_ID)
WHERE licence LIKE '%Blue%' **OR** licence LIKE '%White%'

of course returns me people that have either, NOT both.

Using:

SELECT distinctrow person.ID AS client_ID, postcode, locality 
FROM (person INNER JOIN licences ON person.ID = licences.client_ID)
WHERE licence LIKE '%Blue%' **AND** licence LIKE '%White%'

of course returns no results (how could it?).

This is a really simplified example, as I am pulling 5 tables together in a nested JOIN, that all have criteria (coming in on 40-odd $_GET variables!) ... but this is where I am stuck. I have used AND in the INNER JOIN, instead of the WHERE, without any change in the outcome. I've drunk lots of beer, still no change :)

I know I am revealing my lack of knowledge of even the basics, but surely I don't need to put SELECTs within SELECTs? LEFT or RIGHT JOIN, perhaps?

I feel I am about to learn something elementary here, so bring it ON!

Answer 1

SELECT  *
FROM    person p
WHERE   (
        SELECT  COUNT(DISTINCT licence)
        FROM    licences l
        WHERE   l.client_id = p.id
                AND l.licence IN ('white', 'blue')
        ) = 2

or

SELECT  DISTINCT p.*
FROM    person p
JOIN    licences l
ON      l.client_id = p.id
        AND l.licence IN ('white', 'blue')
GROUP BY
        p.id
HAVING  COUNT(DISTINCT licence) = 2

Depending on data distribution (how many licenses per person do you have), one of the queries will be more efficient.

Answer 2

Try this:

SELECT person.*
FROM person
WHERE EXISTS (SELECT 1 FROM licenses 
               WHERE licenses.client_ID = person.ID 
                 AND licence LIKE '%Blue%')
AND   EXISTS (SELECT 1 FROM licenses 
               WHERE licenses.client_ID = person.ID 
                 AND licence LIKE '%White%')

Depending on your schema and table size, this might be quite slow, though

Answer 3

Sounds like an INTERSECT would work. You could select all the blues INTERSECT select all the whites.

SELECT distinctrow person.ID AS client_ID, postcode, locality 
FROM person INNER JOIN licences ON person.ID = licences.client_ID
WHERE licence LIKE '%Blue%'
INTERSECT
SELECT distinctrow person.ID AS client_ID, postcode, locality 
FROM person INNER JOIN licences ON person.ID = licences.client_ID
WHERE licence LIKE '%White%'

(Or something similar. I'm not very familiar with MySql.)

EDIT: Never mind, MySql doesn't support it. That's what I get for trying to answer a MySql question

Answer 4

Since a blue license and a white license each exist in a separate record in the licenses table, you'll need to join that table twice, like so:

SELECT person.ID AS client_ID, postcode, locality 
FROM person 
JOIN licences l1 ON person.ID = l1.client_ID
JOIN licences l2 ON person.ID = l2.client_ID
WHERE l1.license LIKE '%Blue%' AND l2.license LIKE '%White%'