Inner join between two tables

Question

Inner join between two tables

Navigation

#1 by (6 votes)

4

I tried it like this 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 inner join ? and now the structure (modeling) of these two tables is correct or is there another way to do it?

mysql query

asked by anonymous 13.10.2014 / 14:59

1 answer

Maximum number of characters in a String and StringBuffer in Java Search performance involving multiple disks

score 6 · Accepted Answer

Your JOIN will work only if the product has the same idQualityGate in all three columns you have for it ( idQualityGate , idQualityGate2 , idQualityGate3 ).

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

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

This can also be written like this:

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

If you want 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 required from each table, instead of getting everything with * .