MySQL and PostgreSQL – Select Rows Where One Row Meets a Condition

MySQLpostgresql

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.

Best Answer

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:

Related Question