Could anyone help me while running query in Postgresql. I'm getting a duplicate record.
SELECT a.id, b.parameter_id
FROM products a
LEFT JOIN products_parameters b ON b.product_id = a.id
WHERE a.category_id = 14 AND b.parameter_id = 22
ORDER BY a.id
Result - duplicated rows:
My data from table products:
SELECT *
FROM "products"
WHERE "id" = '10'
My data from table products_parameters:
SELECT *
FROM "products_parameters"
WHERE "product_id" = '10'
What is wrong with my query?
If you don't want duplicates, the simplest solution is to use select distinct:
SELECT DISTINCT p.id, pp.parameter_id
FROM products a JOIN
products_parameters pp
ON pp.product_id = p.id
WHERE p.category_id = 14 AND pp.parameter_id = 22
ORDER BY p.id;
Your question doesn't have enough information to specify why you are getting duplicates, but presumably because you are only choosing one column from each of the tables and other columns are different.
Note the other changes to the query:
WHERE clause turns the LEFT JOIN into an inner join anyway, so this version properly expresses the JOIN.
