mysql join multiple where

Question

I have a sql query. The Where clause is a little tricky, because I need it to tell that fv.position should be greater than a number and id_feature a specific number, but only in that state. So there could be multiple where stack's like that.

This is what I have come up with so far:

SELECT
    *
FROM
    psx_product p
INNER JOIN
    psx_feature_product fp ON fp.id_product = p.id_product
INNER JOIN
    psx_feature_value fv ON fp.`id_feature_value` = fv.`id_feature_value`
WHERE 
    (fv.position > 0 AND fp.id_feature = 6)
AND
    (fv.position > 5 AND fp.id_feature = 7)
AND
    (fv.position > 2 AND fp.id_feature = 8)

But I believe that the above example is the wrong way of handling it, because fv.position doesen't know that it's connected to only the feature in the perentesis, and stacking the where statements, just give empty result, where, one gives results. How should this be done right?

I see no reason for your code doesn't work. But, the conditions will never be supplied because all the AND operators must be true in this case and you never gonna have a id feature that is at same time 6, 7 and 8. You will need some OR operators between each condition out of parentheses — edson alves
– edson alves, Commented Sep 27, 2018 at 21:53

Guy Louzon · Accepted Answer · 2018-09-27 21:39:18Z

1

If I understand correctly you'rr need ORs between the cases, instead of ANDs, in the where clause obviously a field can't be equal 2 different values at the same time

If there is a formula that sets the parameters of fv.position and fp.id_feature then you can save all the specific conditions and write a specific one.

    SELECT * FROM psx_product p
 INNER JOIN psx_feature_product fp ON fp.id_product = p.id_product
 INNER JOIN psx_feature_value fv ON fp.`id_feature_value` = fv.`id_feature_value` WHERE
 (fv.position > 0 AND fp.id_feature = 6) OR
 (fv.position > 5 AND fp.id_feature = 7) OR
 (fv.position > 2 AND fp.id_feature = 8)

answered Sep 27, 2018 at 21:39

Guy Louzon

1,21310 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

simon Over a year ago

Yes. That Is correctly understood. Though when I use the OR statement, it gives me double results, like 3 times with product id's of 139 etc.

simon Over a year ago

I think I have acatually solved it, by using GROUP BY p.id_product. Would that be a good solution for the doubles?

Guy Louzon Over a year ago

I don't know what's the relationship between the tablea and what you're trying to achieve, but the usw of group by should help

Guy Louzon Over a year ago

@simon yes, depending on the relationshipa of the tablea ans your goal

Collectives™ on Stack Overflow

mysql join multiple where

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related