Query Doubt

Question

Query Doubt

Joy Smith San

SSC-Insane

Points: 24877
More actions
January 19, 2011 at 3:33 am

#388668

Hi All,
I need you help in writing a query.
Let's say I have 2 tables caled tblEMPTable and tblEmp_Sal_Components.
Following recrods are there in those 2 tables
tblEMPTable
============
EmpNoName
----------------
emp_1aaa
emp_2bbb
emp_3ccc
emp_4ddd
emp_5eee
emp_6fff
emp_7ggg
emp_8hhh
emp_9iii
tblEmp_Sal_Components
======================
EmpNoCompId
----------------
emp_1cmp_1
emp_1cmp_2
emp_1cmp_3
emp_1cmp_5
emp_2cmp_1
emp_2cmp_2
emp_3cmp_2
emp_3cmp_3
emp_4cmp_1
emp_4cmp_2
emp_4cmp_3
emp_4cmp_4
emp_5cmp_1
emp_5cmp_2
emp_5cmp_3
emp_6cmp_3
emp_7cmp_2
emp_7cmp_3
emp_8cmp_1
emp_8cmp_2
emp_8cmp_3
emp_8cmp_6
Now I want to write 2 queries.
1. Want to find out all those employees who has got all the three components cmp_1, cmp_2, cmp_3 must have all the 3)
2. Want to find out all those employees who has got all the three components cmp_1, cmp_2, cmp_3 (must have all the 3) and who doesn't have cmp_4 component.
I have written a query to get the same result in my own way.
Would like to see if there's a better way.
Thanks in advance.,

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Joy Smith San (1/19/2011)
...
I have written a query to get the same result in my own way.
Would like to see if there's a better way.
...

Please post what you've tried so we can make sure to completely understand your requirement.

Right now I'm wondering what I would need the tblEMPTable table for...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 2

Thanks for your reply.

Requirement is simple...

Want to pick those records from EMP table which has got all following components in the second table.... nothing else...

Cmp_1, cmp_2, cmp_3

Thanks aagain

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

SELECT EmpId, SUM(CASE WHEN ColName IN ('comp1', 'comp2', 'comp3') THEN 1 ELSE 0 END) AS HasAllItems, COUNT(*) AS ItemsCnt FROM #tmp GROUP BY EmpId HAVING SUM(CASE WHEN ColName IN ('comp1', 'comp2', 'comp3') THEN 1 ELSE 0 END) = COUNT(*)

The having validates that the employee only has 3 items and nothing else, but I'm not sure that's what you wanted.

If the employee needs all 3 comps and can have other comps :

SELECT EmpId, COUNT(*) AS ItemsCnt FROM #tmp

WHERE ColName IN ('comp1', 'comp2', 'comp3')

GROUP BY EmpId

HAVING COUNT(*) = 3

You can also use a split function if the input param is a csv. Use the split in the where and then again for the count. That way everything is dynamic.

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 4

Thank you somuch.

Infact I had used the second option...

The exact requirement was I had to select all those emps with all those 3 components and from that list I have EXCLUDE those employees who has got comp_4 as well.

I did it by writing an outer query on it.

Thanks again.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

SELECT EmpId, SUM(CASE WHEN ColName IN ('comp1', 'comp2', 'comp3') THEN 1 ELSE 0 END) AS HasAllItems, COUNT(*) AS ItemsCnt FROM #tmp GROUP BY EmpId HAVING SUM(CASE WHEN ColName IN (Select Value dbo.fnSplit('comp1,comp2,comp3', ',')) THEN 1 ELSE 0 END) - SUM(CASE WHEN ColName IN ('comp4') THEN 1 ELSE 0 END) = (SELECT COUNT(*) FROM dbo.fnSplit('comp1,comp2,comp3', ','))

That way you can still use a split function in all parameters :hehe:.

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 6

Joy Smith San

SSC-Insane

Points: 24877

January 19, 2011 at 11:42 pm

#1275684

Thank you somuch. Am clear now.