CODEWITHSUNDEEP
sqlpostgresqlduplicates
Mantoze
Mantoze

Reputation: 75

duplicate records from postgresql sql query

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:

enter image description here

My data from table products:

SELECT *
FROM "products"
WHERE "id" = '10'

enter image description here

My data from table products_parameters:

SELECT *
FROM "products_parameters"
WHERE "product_id" = '10'

enter image description here

What is wrong with my query?

Upvotes: 0

Views: 2715

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

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:

  • The table aliases are meaningful rather than arbitrary letter.
  • The WHERE clause turns the LEFT JOIN into an inner join anyway, so this version properly expresses the JOIN.

Upvotes: 1

Related Questions