0

I have a table with 3 columns:

UserId  ProductId    Status
1       100          1
1       1            0
2       100          0
4       100          0
5       1            1
5       100          1
6       2            1

I want to select all rows that have ProductId = 100 at least once per user and the user has at least one more record other than with ProductId = 100.

Expected results:

UserId  ProductId    Status
1       100          1
1       1            0
5       1            1
5       100          1

I solved it like this by referring to another solution:

select "ProductId", "UserId", "Status"
from "MyTable"
WHERE EXISTS (
    SELECT 1 FROM "MyTable" WHERE "ProductId" = 100
)

But it returns all users where even one record is 100.

2 Answers 2

1

If you can handle both products on the same row:

select t1.UserId, t1.ProductId, t1.Status,
       t2.ProductId as otherproduct, t2.Status as otherproductstatus
from MyTable t1
join MyTable t2
  on t1.UserID=t2.UserID
     AND t1.ProductID=100
     AND t2.productID!=100

However with this you'll get all products for a user:

1

For PostgreSQL you can use

WITH cte AS (SELECT id FROM MyTable WHERE ProductID=100
             INTERSECT ALL
             SELECT id FROM MyTable WHERE ProductID!=100)
SELECT MyTable.*
FROM MyTable 
NATURAL JOIN cte

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.