SQL JOIN using mapping table

Question

I have three tables:

COLLECTION PERSON PERSON_COLLECTION 

where PERSON_COLLECTION is the mapping table id|person_id|collection_id

Now I want to select all the entries in the collection and sort them by person.name .

Do I need to join separate tables with a mapping table first, and then join the results again?

Answer 1

 SELECT c.*, p.Name FROM Collection c JOIN Person_Collection pc ON pc.collection_id = c.id JOIN Person p ON p.id = pc.person_id ORDER BY p.Name 

Answer 2

The order you attach will not violate it, but depending on which sql product you are using, it may affect performance. You need to decide whether you want ALL records from both tables or only records that have a matching mapping record, this will change the type of connection you need to use.

Answer 3

Not sure without a table schema, but I take this:

 SELECT c.*, p.* FROM Person_Collection pc LEFT JOIN Collection c ON pc.collection_id = c.id LEFT JOIN Person p ON pc.person_id = p.id ORDER BY p.name