How to select data with joins from database without duplication-CodePudding

I do have query which is returning correct data, but I would like to modify it to return only unique values from left tables.

SELECT * FROM XUSER u
LEFT JOIN USER_PRODUCT up ON u.id = up.user_id
LEFT JOIN PRODUCT p ON up.product_id = p.id
WHERE p.id IN (1,2,3...18);

So as you probablu suspect if User with id=1 has multiple relation to product I will get displayed multiple times this user.

I can really user DISTINCT as I need some data from product table but single result would be ok. For example if user whould have relation to product called book and movie, I am ok to displayed only first relation with text BOOK.

Same goes with order by.

CodePudding user response：

It seems you simply need a ROW_NUMBER() analytic function -

SELECT * FROM XUSER u
LEFT JOIN USER_PRODUCT up ON u.id = up.user_id
LEFT JOIN (SELECT id, <other required columns>, 
                  ROW_NUMBER() OVER(PARTITION BY id) RN
             FROM PRODUCT) p ON up.product_id = p.id
                            AND RN = 1
WHERE p.id IN (1,2,3...18);

CodePudding user response：

There is a way to simplify it. I would also index product_id in USER_PRODUCT table. I also wonder where do you have duplicate entries? Are they in USER_PRODUCT or PRODUCT tables?

SELECT *
FROM XUSER u
    JOIN
    (
        SELECT user_id, product_id
        FROM USER_PRODUCT up
        GROUP BY user_id, product_id
    ) gp
        ON gp.user_id = u.id
    JOIN PRODUCT p
        ON p.id = gp.product_id
WHERE p.id IN ( 1, 2, 3, 18 );