CODEWITHSUNDEEP
mysqlsqljoin
Sydowh
Sydowh

Reputation: 99

JOIN three MySQL table

I got three tables :

Three tables

== liste_caracteristiques
|------
|------
|//**idCarac**//|int(11)|Non|
|//**refproduit**//|int(11)|Non|
|valeur|varchar(255)|Oui|NULL
|0|4942|Windows
|0|8149|Android
|1|9980|300
|1|10300|1100
|1|10316|149.5
|2|4942|7
|2|8149|12
|2|9980|15cm
|2|10300|4cm
|2|10316|8.3cm

and

== caracteristiques
|------
|------
|//**idCarac**//|int(11)|Non|
|libelleCarac|varchar(255)|Oui|NULL
|0|Système d'exploitation
|1|Poids
|2|Screen size
|3|ATEX
|4|Zone 0
|5|Zone 1
|6|Zone 2

and

== types_carac
|------
|------
|//**idTypeProd**//|int(11)|Non|
|//**idCarac**//|int(11)|Non|
|1|1
|1|2
|1|3
|2|0
|2|2
|2|3

I need to get all value of "valeur" when libelleCarac is "Screen size" and when idTypeProd = 2

So I tried to use :

SELECT * 
FROM types_carac as T, caracteristiques as C, liste_caracteristiques as L 
WHERE T.idCarac = C.idCarac 
AND C.idCarac = L.idCarac 
AND idTypeProd = 2 
AND libelleCarac = "Screen size"

In my result some values are not linked with idTypeProd = 2

I have : 

|idTypeProd|idCarac|idCarac|libelleCarac|idCarac|refproduit|valeur
|2|2|2|Screen size|2|4942|7
|2|2|2|Screen size|2|8149|12
|2|2|2|Screen size|2|9980|15cm
|2|2|2|Screen size|2|10300|4cm
|2|2|2|Screen size|2|10316|8.3cm

And I need

|idTypeProd|idCarac|idCarac|libelleCarac|idCarac|refproduit|valeur
|2|2|2|Screen size|2|4942|7
|2|2|2|Screen size|2|8149|12

In fact I just need the Screen Size value of idType = 2, my query looks good to me but results aren't

Upvotes: 0

Views: 89

Answers (2)

WhatsThePoint
WhatsThePoint

Reputation: 3635

let me know if this works. it uses the modern joining syntax which is easier to read and debug if there are errors. this query joins your three tables together and then returns your valeur value where idTypeProd is 2 and libelleCarac is screen size.

SELECT  valeur
FROM    liste_caracteristiques lc
JOIN    caracteristiques c on c.idCarac = lc.idCarac
JOIN    types_carac tc on tc.idCarac = c.idCarac
WHERE   tc.idTypeProd = 2
AND     libelleCarac = "Screen size"

Upvotes: 1

Valerica
Valerica

Reputation: 1630

You can try this query, using JOIN as explained above, in the comment section:

SELECT
    TC.idTypeProd
    ,LC.idCarac
    ,C.libelleCarac
    ,LC.refproduit
    ,LC.valeur
FROM liste_caracteristiques LC
INNER JOIN types_carac TC
    ON LC.idCarac = TC.idCarac
    AND idTypeProd = 2
INNER JOIN caracteristiques C
    ON TC.idCarac = C.idCarac
    AND libelleCarac = 'Screen size'

Upvotes: 0

Related Questions