CODEWITHSUNDEEP
mysql
J for J
J for J

Reputation: 35

Get data using group row conditions

Users Table

|user_id |   user_email  |
|1       | [email protected]  |
|2       | [email protected]  |

Relations Table

id | user_id | user_type |   action   |
1  | 1       | 1         |  Sign      |
2  | 1       | 2         |  Waiting   |
1  | 2       | 1         |  Sign      |
2  | 2       | 2         |  Sign      |

I want to get Data which user_type = 1 and action = sign but user_type = 2 and action is not sign for the same user_id.

So output looks like

id |    email     | user_id | user_type |   action   |
2  | [email protected] | 1       | 2         |  Waiting   |

Here is what i did as per my knowlege.

SELECT * FROM relations WHERE (
    CASE
        WHEN user_type = 1 AND action = 'Sign'
        THEN user_type = 2 AND action != 'Sign'
    END
) GROUP BY user_id;

Upvotes: 0

Views: 43

Answers (1)

Zakaria
Zakaria

Reputation: 4796

This should do it:

select r.id, u.user_email, r.user_id, r.user_type, r.action
from relations r inner join users u on r.user_id = u.user_id
where action <> 'Sign' and exists (select id from relations where user_id = r.user_id and action = 'Sign')

Basically check for each row where action is different of sign if there is a row with the same user_id where the action is sign. Join The two tables to get the email.

Fiddle

Upvotes: 1

Related Questions