Grouping with status

[SqlHunter]

Here iam stuck to get a combination of query for reportings.....

Create table tbl_emp_status
(
emp_id varchar(20),
std_id int,
status char(1)
)

insert into tbl_emp_status
(emp_id,std_id,status)
values
(1005370,2323,'N')

insert into tbl_emp_status
(emp_id,std_id,status)
values
(1005370,2325,'N')


insert into tbl_emp_status
(emp_id,std_id,status)
values
(1005370,2326,'Y')

insert into tbl_emp_status
(emp_id,std_id,status)
values
(1054170,3339,'N')

insert into tbl_emp_status
(emp_id,std_id,status)
values
(1054170,3337,'N')

insert into tbl_emp_status
(emp_id,std_id,status)
values
(1054170,3446,'N')


insert into tbl_emp_status
(emp_id,std_id,status)
values
(1054179,2339,'Y')

insert into tbl_emp_status
(emp_id,std_id,status)
values
(1054179,2337,'Y')

insert into tbl_emp_status
(emp_id,std_id,status)
values
(1054179,2446,'Y')


insert into tbl_emp_status
(emp_id,std_id,status)
values
(1054199,337,'Y')

insert into tbl_emp_status
(emp_id,std_id,status)
values
(1054199,446,'N')


Here I need 3 types of query :-
1.I need the emp_id and std_id with the combinations having just status as only 'Y'
2.I need the emp_id and std_id with the combinations having just status as only 'N'
3.I need the emp_id and std_id with the combinations having just status as 'N' and only one 'Y'


case 1 should give
1054179,2339,'Y'
1054179,2337,'Y'
1054179,2446,'Y'

case 2 should give

1054170,3339,'N'
1054170,3337,'N'
1054170,3446,'N'

case 3 should give

1005370,2323,'N'
1005370,2325,'N'
1005370,2326,'Y'

1054199,337,'Y'
1054199,446,'N'

 

[JamesLean]

I think these should give you want you need (at least they worked on your test data!)

--query 1
SELECT t1.*
FROM tbl_emp_status t1
	LEFT JOIN (
		SELECT DISTINCT emp_id
		FROM tbl_emp_status
		WHERE status = 'N'
	) t2 ON t1.emp_id = t2.emp_id
WHERE t2.emp_id IS NULL

--query 2
SELECT t1.*
FROM tbl_emp_status t1
	LEFT JOIN (
		SELECT DISTINCT emp_id
		FROM tbl_emp_status
		WHERE status = 'Y'
	) t2 ON t1.emp_id = t2.emp_id
WHERE t2.emp_id IS NULL

--query 3
SELECT t1.*
FROM tbl_emp_status t1
	JOIN (
		SELECT emp_id,
			SUM(CASE WHEN status = 'Y' THEN 1 ELSE 0 END) AS numY,
			SUM(CASE WHEN status = 'N' THEN 1 ELSE 0 END) AS numN
		FROM tbl_emp_status
		GROUP BY emp_id
	) t2 ON t1.emp_id = t2.emp_id
WHERE t2.numY = 1
	AND t2.numN > 0

--James