How to perform WHERE in with multiple columns in postgres-CodePudding

I wants to do something like this,

SELECT * FROM product p
                    JOIN product_version pv ON p.id = pv.product_id
                    where (p.code, pv.product_version) in (("FF6",1), ("FF12", 1));

But this is giving error at in clause. Can someone provide the correct syntax.

CodePudding user response：

You are not providing any information about the actual error neither about column types.

But, by the way, it really looks like that those double quotes are wrong because in Postgres strings are quoted using simple quotes ('), not double (").

Try:

SELECT *
FROM product p
JOIN product_version pv ON (p.id = pv.product_id)
where
    (p.code, pv.product_version) in (('FF6',1), ('FF12', 1))
;

Despite that, your query looks syntactically "correct" unless some kind of type mismatching we cannot foresee without more information.

CodePudding user response：

You probably can't go with IN, depending on your goal you need to do something like:

where (p.code = "FF6" and pv.product_version = 1) or
(p.code = "FF12" and pv.product_version = 1)

or, if the logic above was not what you meant, maybe:


where p.code IN ("FF6", "FF12} AND pv.product_version IN (1)


where p.code IN ("FF6", "FF12} OR pv.product_version IN (1)

CodePudding user response：

This code should work for you

SELECT * FROM product p
                    JOIN product_version pv ON p.id = pv.product_id
                    where p.code in("FF6","FF12") and pv.product_version = 1