Query Help

[jacob21]

Hi,
Need suggestion for sql query.
I have 2 tables
user user_status

user: id,name
user_status=id,user_id,status_type,status_value

user_status stores multiple status_type and status_value.for 1 user there can be multiple rows

I am trying to get all users having who are not blocked i.e user_status table dont have status_type(10) and status_value(11)

[cereal]

Hi,

in this case you can use a subquery to return all the rows from user_status: selecting their max(status_type) and max(status_value) and grouping by the user_id. Then you need a row constructor:

select id, name from user, (select user_id, max(status_type) as status_type, max(status_value) as status_value from user_status group by user_id) as sub where id = sub.user_id and (status_type,status_value) != (10,11);

Live example: http://sqlfiddle.com/#!2/b1171/3
Documentation: http://dev.mysql.com/doc/refman/5.6/en/row-subqueries.html

[rch1231]

I would use count to see if the user ID returns any rows where status_type(10) and status_value(11). and use that as the having part of you query.

select id, 
name,
(select count(*) from user_status where status_type=10 and status_value=11) as mycount
having mycount > 1