Records matching multiple criteria

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Records matching multiple criteria

Author

Topic

davenims
Starting Member

21 Posts

Posted - 2007-01-09 : 05:05:28

Hello, could anyone help with the following syntax? Say I have the following example table storing employees and the departments they work for:

Employee 	Department
--------	----------
Smith		Accounts
Jones		Sales
Davies		Sales
McDonald	Sales
Smith		HR
McDonald	HR
Jones		Accounts

One employee can belong to many departments.

I want to specify 2 or more names, and return the department which matches all those employees.

So, for example, if I was to specify "Smith" and "Jones", "Accounts" should be returned, since that department is made up of all the specified employees.

If I specified "Smith" and "McDonald", no records should be returned since there is no department containing both those employees.

If I specified "Jones" and "Davies", no records should be returned, since both employees work for Sales, however "McDonald" also works for Sales but has not been specified.

SELECT Department FROM Example WHERE Employee = 'Smith' OR Employee = 'Jones'

will not return the correct results, as it will return all departments where either of these employees work.

TIA...

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-09 : 05:13:51

select t.Department
from tbl t
where t.Employee in ('Smith', 'Jones')
group by t.Department
having count(*) = 2

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-09 : 05:18:14

[code]-- prepare sample data
declare @t table (employee varchar(20), department varchar(20))

insert @t
select 'Smith', 'Accounts' union all
select 'Jones', 'Sales' union all
select 'Davies', 'Sales' union all
select 'McDonald', 'Sales' union all
select 'Smith', 'HR' union all
select 'McDonald', 'HR' union all
select 'Jones', 'Accounts'

-- show the data 1
select department
from @t
where employee = 'jones'
and department in (select department from @t where employee = 'davies')
and department in (select department from @t where employee = 'mcdonald')

-- show the data 2
select department
from @t
where employee in ('smith', 'jones')
group by department
having count(*) = 2[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-09 : 05:18:37

Peter Larsson
Helsingborg, Sweden

davenims
Starting Member

21 Posts

Posted - 2007-01-09 : 05:24:05

Hi, thanks for the responses, these look close, however one thing is still wrong and that's that, say if I specify "Jones" and "Davies", all the above will still return "Sales", even though the other employee in that department, i.e. "McDonald" is missing...

davenims
Starting Member

21 Posts

Posted - 2007-01-09 : 05:37:11

OK, I think I did it with a modification to the above...

SELECT     Department
FROM         Example
WHERE     (Employee IN ('Jones', 'Davies'))
GROUP BY Department
HAVING      (COUNT(*) = 2) AND
                          ((SELECT     COUNT(*)
                              FROM         Example t
                              WHERE     t.Department = Example.Department AND Employee NOT IN ('Jones', 'Davies')) = 0)

Thanks for the pointers Peso and nr

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-09 : 05:46:05

[code]-- prepare sample data
declare @t table (employee varchar(20), department varchar(20))

insert @t
select 'Smith', 'Accounts' union all
select 'Jones', 'Sales' union all
select 'Davies', 'Sales' union all
select 'McDonald', 'Sales' union all
select 'Smith', 'HR' union all
select 'McDonald', 'HR' union all
select 'Jones', 'Accounts'

-- show the data 3
select department
from @t
group by department
having count(*) = sum(case when employee in ('smith', 'mcdonald') then 1 else 0 end)

-- show the data 4
select department
from @t
group by department
having count(*) = sum(case when employee in ('jones', 'davies') then 1 else 0 end)[/code]

Peter Larsson
Helsingborg, Sweden

davenims
Starting Member

21 Posts

Posted - 2007-01-09 : 06:05:23

Excellent, that's a bit more elegant looking than mine, thanks! :)

OK, I don't want to push it now, but say this was the case:

quote:
The user specifies the name of 2 or more employees...

- The query should return a list, specifying either the name of the department, if the whole department is present OR the name of the employee, if they are present but the whole of the department is not.

- For the purpose of this list, one employee may only belong to one department. If they belong to 2 departments, they will take up position in the largest of the departments which they belong, or if the departments are the same size, the department coming first when sorted in alphabetical order.

So, for example, if I were to specify Smith, Jones and McDonald, the result returned would be:
Accounts
McDonald

Because Smith and Jones (of Accounts) are both present. Smith belongs to Accounts and HR, but takes position in Accounts because this comes first alphabetically.

McDonald belongs to Sales, but does not take position in this because Davies is not present, and also HR , but does not take position in this because Smith is the other member of this department, but has already taken position in Accounts. So McDonald takes position individually.

If you tell me that this can only be done programatically in the client app, or with an evil SQL cursor, then that is fine, I'm just curious to see if there's a way to do this in a T-SQL query...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-09 : 08:14:43

Use either dynamic SQL, or have an "employee matching table"...

Peter Larsson
Helsingborg, Sweden

davenims
Starting Member

21 Posts

Posted - 2007-01-09 : 09:06:32

OK, cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources