Simple? join question 2

[wbodger]

OK, let's say I have a table with staffid, protocolid, userrightid and projectid where those four together are unique. I want a query that gives me the staffid (which can be in the table multiple times) for every staffid that has ONLY a certain userrightid within a certain projectid. To be specific, only userrightid=100 where projectid=3. I have done this with three subqueries (because I then use that StaffID to query from another table), but am trying to do away with subqueries. So, if a staffid has four entries and three of them have userrightid=100, but one has userrightid=3, then I do not want them in my list. I believe this query is correct

SELECT distinct FirstName,LastName,Login, StaffID
FROM db.dbo.tblStaff
where StaffID in (
	select distinct staffid
	from [db].[dbo].[tblStaffProtocol]
	where StaffID in 
	(
		SELECT [StaffID]
		FROM [db].[dbo].[tblStaffProtocol]
		WHERE ProjectID=3
		and UserRightID=100
	)
	AND StaffID not in 
	(
		SELECT DISTINCT [StaffID]
		FROM [db].[dbo].[tblStaffProtocol]
		where UserRightID<>100
		and ProjectID=3
	)
)

it just feels kludgy to me. Any thoughts?

wb

 

[gmmastros]

Can you show some sample data and expected results?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

So, as a simple example

staffid	protocolid	userrightid	ProjectID
759	109	        6	        3
759	108	        100	        3
759	0	        100     	3
761	121	        100	        3
761	111	        100	        3
761	108	        100	        3
761	185	        100	        3
761	195	        100	        3
761	120	        100	        3
761	0	        100	        3

I would expect to see staffid 761 in my list because they ONLY have UserRightID=100 whereas I would not want to see staffid 759 in my list because while there are two UserRightID = 100, there is one UserRightID <> 100 for that staffid.

Thanks,
wb

 

[imex]

Try:

with CTE_S as
(
    select staffid
    from [db].[dbo].[tblStaffProtocol]
    where ProjectID = 3
    group by staffid
    having COUNT(userrightid) = COUNT(case when userrightid = 100 then 1 end)
)

SELECT distinct s.FirstName, s.LastName, s.Login, s.StaffID
FROM db.dbo.tblStaff as s
INNER JOIN CTE_S as c
    on c.Staffid = s.StaffID

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[wbodger]

So, that gives me the same result as my ugly query. Nice. Now, I don't quite understand what the having clause is doing, or perhaps why that works.

 

[gmmastros]

My suggestion would have been very similar to imex's post.

They key here is the COUNT function. This is considered an aggregate function, and therefore requires a group by clause in the query.

There are several things to understand about the Count function.

Count(*) or Count(1) will count the number of rows.
Count(ColumnName) will count the number of rows where the value in ColumnName is not null.

The query that imex shows has...

[tt]COUNT(case when userrightid = 100 then 1 end)[/tt]

Notice that there is no ELSE clause in the case expression. When there is no ELSE, NULL is returned if it does not match the case statement. In this situation, if the value stored in UserRightId is 100, then 1 is returned, otherwise NULL is returned. We then count the number of 1's.

Make sense?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

Oh, so it gives the staffids where the number of records with userrightid=100 is equal to the total number of records for that staffid. Got it. Makes sense now, thank you!