Inner Join between two tables

Question

I tried it this way but it’s wrong

SELECT * FROM produto 
INNER JOIN familiaproduto 
ON familiaproduto.idfamiliaproduto = produto.idNomeProduto 
INNER JOIN qualitygate ON (qualitygate.idQualityGate = produto.idQualityGate1 and qualitygate.idQualityGate = produto.idQualityGate2 and qualitygate.idQualityGate = produto.idQualityGate3)

How do I do this inner join? and by the way the structure (modeling) of these two tables is correct or there is another way of doing?

Answer 1

This one of yours JOIN will work only if the product has the same idQualityGate in all three columns you have for this (idQualityGate, idQualityGate2, idQualityGate3).

Looks like you want one OR, which will find records if at least one of the 3 fields exists in the table Quality_Gate:

INNER JOIN qualitygate 
ON qualitygate.idQualityGate = produto.idQualityGate1 OR 
   qualitygate.idQualityGate = produto.idQualityGate2 OR 
   qualitygate.idQualityGate = produto.idQualityGate3

It can also be written like this:

INNER JOIN qualitygate 
ON qualitygate.idQualityGate IN (produto.idQualityGate1, produto.idQualityGate2, produto.idQualityGate3)

If what you want is to bring 3 quality Gates per product, you need 3 Joins:

SELECT * FROM produto 
INNER JOIN familiaproduto 
ON familiaproduto.idfamiliaproduto = produto.idNomeProduto
INNER JOIN qualitygate qualitygate1 
ON qualitygate1.idQualityGate = produto.idQualityGate1
INNER JOIN qualitygate qualitygate2
ON qualitygate2.idQualityGate = produto.idQualityGate2
INNER JOIN qualitygate qualitygate3
ON qualitygate3.idQualityGate = produto.idQualityGate3

I would still recommend selecting only the necessary from each table instead of picking everything up with the *.