CODEWITHSUNDEEP
sqldatabase-designforeign-keys
macstens
macstens

Reputation: 33

Multiple references on one separate table-column possible?

Given is following database-structure:

Products:

- id
- model
- manufacturerID
- subcontractorID

manufacturerID and subcontractorID are referencing to one and the same table, because some manufacturers are also subcontractors:

Manufacturers:

- id
- name

Now, if I want to select both of these columns, how do I do that?

I tried something like this...

SELECT p.model, m.name AS Manufacturer, m.name AS Subcontractor
FROM Product AS p
LEFT JOIN Manufacturers AS m ON p.manufacturerID = m.id
LEFT JOIN m ON p.subcontractorID = m.id

... but I could only get one of both column-values.

Upvotes: 1

Views: 79

Answers (2)

devarc
devarc

Reputation: 1157

Try this one:

LEFT JOIN Manufacturers AS mman ON p.manufacturerID = mman.id
LEFT JOIN Manufacturers AS msub ON p.subcontractorID = msub.id

Upvotes: 0

RB.
RB.

Reputation: 37182

Close - you need to join the Manufacturer table twice:

SELECT 
     p.model, manufacturer.name AS Manufacturer, 
     subcontractor.name AS Subcontractor
FROM Product AS p LEFT JOIN 
     Manufacturers AS manufacturer  
     ON p.manufacturerID = manufacturer.id LEFT JOIN 
     Manufacturers AS subcontractor 
     ON p.subcontractorID = subcontractor.id

Upvotes: 1

Related Questions