CODEWITHSUNDEEP
sqlinner-join
user871784
user871784

Reputation: 1257

SQL JOIN using a mapping table

I have three tables: 

COLLECTION 
PERSON 
PERSON_COLLECTION

where PERSON_COLLECTION is a mapping table id|person_id|collection_id

I now want to select all entries in collection and order them by person.name.

Do I have to join the separate tables with the mapping table first and then do a join again on the results?

Upvotes: 12

Views: 20391

Answers (3)

rossmcbain
rossmcbain

Reputation: 129

Not sure without the table schema but, my take is:

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

Upvotes: 1

bendataclear
bendataclear

Reputation: 3850

The order you join won't break it but depending on which sql product you're using may effect performance. You need to decide if you want ALL records from both/either table or only records which have a matching mapping entry, this will change the type of join you need to use.

Upvotes: 0

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

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

Upvotes: 13

Related Questions