PostgreSQL returning duplicate IDs when using IN in a JOIN

Question

I have two tables with the following information

PRODUCTS
id     |description       |featureImage
13     |a phone           | www.image.com/image23
14     |a phone accessory | www.image.com/image24
15     |another accessory | www.image.com/image25


Product Accessories
id     |productId         |accessoryId
1      |13                |14
2      |13                |15

When using a query as follows

 SELECT DISTINCT ON (accessories.id, products.description)
        products.description, products.featureImage, accessories.id 
 FROM schema.accessories 
 JOIN schema.products ON products.id IN 
   (SELECT accessories.accessoryId FROM accessories
    WHERE accessories.id IN (14,15))

I am getting duplicate results as

 description        |featureImage     |id
 a phone accessory  |image.com/image24|14
 a phone accessory  |image.com/image24|15
 another accessory  |image.com/image25|14
 another accessory  |image.com/image25|15

The result set I was expecting would be

description         |featureImage     |id
a phone accessory   |image.com/image24|14
another accessory   |image.com/image25|15

So I would search the table for products with the original product id, and then get the ids of the rows with accessories for that product. Once I get those they are used for the sub query

(SELECT accessories.accessoryId FROM accessories WHERE accessories.id IN (14,15))

to get the results.

I was thinking that using the DISTINCT ON would stop the duplicates, but I am still getting duplicates, with the id being linked up incorrectly on some of them.

Question is solved, just need to wait for the 8 hours so I can reply and close as answered with the following text;

as per @a_horse_with_no_name by simplifying the query to

SELECT p.description, p.featureimage, a.accessory_id
FROM accessories a 
JOIN products p ON p.id = a.accessory_id

with a Where statement at the end identifying the proper product to find accessories for, the expected result set is returned.

So what would I be looking to do if I wanted to only have the product details, with the id of the row in the accessory table for that accessory? So I would only get a single 'a phone accessory' and a single 'another accessory' with the appropriate id for the row containing that product id as the accessory? — user3245442
– user3245442, Commented Apr 14, 2014 at 14:56
doing DISTINCT ON (accessories.id) returns me a result set with the appropriate different ids, but the description, and featureImage both come back as 'phone acessory' with its featureImage url. — user3245442
– user3245442, Commented Apr 14, 2014 at 15:09
A distinct on only makes sense if you also use an order by. But your join condition looks pretty strange as well. What exactly are you trying to achieve here? — user330315
– user330315, Commented Apr 14, 2014 at 15:18
Isn't that a simple join between products and accessories based on accessory.accessoryId = product.id — user330315
– user330315, Commented Apr 14, 2014 at 15:24

user330315 · Accepted Answer · 2014-04-14 20:07:02Z

1

From the discussion in the comments it seems all you want is a simple join:

SELECT p.description, p.featureimage, a.accessory_id
FROM accessories a 
   JOIN products p ON p.id = a.accessory_id

SQLFiddle example: http://sqlfiddle.com/#!15/7f460/1

answered Apr 14, 2014 at 20:07

user330315

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

Comments

Andreas · Accepted Answer · 2014-04-14 19:50:49Z

0

Try this:

SELECT
    ,p.description
    ,p.featureImage
    ,a.id
FROM SCHEMA.accessories AS a
JOIN SCHEMA.products AS p ON p.id = a.accessoryId
WHERE a.id IN (
        14
        ,15
        )

Note, you may have an error in your example: should WHERE accessories.id IN (14,15) be WHERE accessories.accessoryid IN (14,15)?

answered Apr 14, 2014 at 19:50

Andreas

5,1562 gold badges28 silver badges37 bronze badges

Collectives™ on Stack Overflow

PostgreSQL returning duplicate IDs when using IN in a JOIN

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related