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.
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.
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)
or
where p.code IN ("FF6", "FF12} OR pv.product_version IN (1)
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
