Rick Rick - 6 months ago 18
MySQL Question

mySQL: Get records union from same table

I have 2 tables a car model and car parts. Both are related with a partfitment table whose only fields are the primary keys modelID and partID.
I am trying to display the parts that fit the model car I selected.
The following works fine:

SELECT tblmodel.modelID,
tblmodel.model,
tblparts.part,
tblparts.part_number,
tblparts.description,
tblparts.list_price
FROM (tblmodel
INNER JOIN tblpartfitment
ON tblmodel.modelID = tblpartfitment.modelID)
INNER JOIN tblparts
ON tblpartfitment.partID = tblparts.partID
WHERE tblmodel.modelID = 1;


The tblparts table has the tinyint field named universal_part. I am trying to include all the parts that have universal_part = true in my original query.

In other words always return all parts that are universal and those that are for the modelID specified.

I tried using a union, but get errors.

Answer

I think you can just include the logic in the ON clause:

SELECT m.ModelID, m.Model, p.part, p.part_number, p.description, p.list_price
FROM tblmodel m INNER JOIN
     tblpartfitment pf
     ON m.ModelID = pf.modelID INNER JOIN
     tblparts p
     ON pf.partID = p.partID OR p.universal_part = 1
WHERE m.ModelID = 1;