Soham Navadiya Soham Navadiya - 3 months ago 9
SQL Question

Fetch unique record from database from multiple option

I have table name: goal

id date valueweekmodified goal_type_id user_id
1 2016-01-07 2 1 2016-01-07 16:21:56 3 3
2 2016-01-07 5 1 2016-01-07 16:21:56 1 3
3 2016-01-07 0 1 2016-01-07 17:17:01 3 4
4 2016-01-07 500 1 2016-01-07 17:17:01 1 4
5 2016-01-07 0 1 2016-01-07 17:50:11 3 6
6 2016-01-07 300 1 2016-01-07 17:50:11 1 6
7 2016-01-07 1 1 2016-01-07 17:52:40 3 5


I want to fetch those users who had set the only and only goal_type_id=3.

For Example:
see id 1,2. That record should not be returned because user_id=3 has two goal_type_id=1 and 3.

From above table only id=7 is valid, because only that row contains goal_type_id = 3 (for user_id=5). Rest of all user had set two goal which I don't want.

How can I do this?

Answer
select user_id from goal 
where user_id not in (select user_id from goal where goal_type_id !=3)
and goal_type_id = 3;

for the whole row

select * from goal 
where user_id not in (select user_id from goal where goal_type_id !=3)
and goal_type_id = 3;

or

select id,  date, value, week,modified,   goal_type_id ,user_id
from goal 
where user_id not in (select user_id from goal where goal_type_id !=3)
and goal_type_id = 3;