how to implement a double join in n-to-n relation in MySQL

Question

In my database I have cars and persons. Between the cars and the persons is an n-to-n 'drives'-'is driven by' relationship.

I would like to query the database to get a list of all the cars that are driven by a specific person (say 'john') and that are also driven by other persons. I would like the query to show per car how many (other) persons are driving it.

The above is a simplification of my actual problem. For extra reference; Cars are typically driven by 1 to 4 persons, but 1 person drives up to 5000 cars

Is it possible to do this in a single query, and if so, how?

greetings,

Coen

Answer 1

Does something like this do the trick?

SELECT  p.PersonName, COUNT(d2.PersonId)
FROM    Drives d
        INNER JOIN
                Person p
                ON d.PersonId = p.PersonId
        LEFT JOIN
                Drives d2
                ON d.CarId = d2.CarId
                AND d.PersonId != d2.PersonId
GROUP BY p.PersonName

To restrict to only cars that are drive by other people, change LEFT JOIN to INNER JOIN.