Referencing tables in nested select statements

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

General SQL Server Forums

New to SQL Server Programming

Referencing tables in nested select statements

Author

Topic

liptonIcedTea
Starting Member

26 Posts

Posted - 2008-03-03 : 17:30:37

I'm just wodnering if you have two select statements, one nested inside another, can you reference tables from the outer loop?

for example, say I would like to find all employees who have at least two clients and employees and clients have a one to many relationship.

select *
from Employee e
where
(
select count(*)
from Clients c
where c.EmployeeId = e.EmployeeId
) >= 2

This obviously doesn't work - but how would i go about doing something like this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-03 : 17:46:04

Try this:

SELECT e.EmployeeId, COUNT(*)
FROM Employee e
INNER JOIN Clients c
ON e.EmployeeId = c.EmployeeId
GROUP BY e.EmployeeId
HAVING COUNT(*) >= 2

If you need to see more columns, then you can use this as a derived table. Let us know what you'd like to see though so we can help with that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-03 : 18:04:44

quote:
Originally posted by liptonIcedTea

This obviously doesn't work - but how would i go about doing something like this?

Did you try the query? It looks like it'd work just fine to me, although I think Tara's query would be more effecient.

liptonIcedTea
Starting Member

26 Posts

Posted - 2008-03-03 : 18:12:47

Ok... I think I simplified the query too much.

What i really want to do is this...

There is 3 tables. Employees, Clients and ClientType. (clientType is a reference table on Client)

basically, I want to find all the employees who have more Clients with ClientType = 'A' then clientType = 'B'.

so i think i'm going to need a nested select statement along the lines of something like this...

SELECT e.EmployeeId, COUNT(*)
FROM Employee e
INNER JOIN Clients c
ON e.EmployeeId = c.EmployeeId
inner join ClientType ct on ct.ClientTypeId = c.ClientTypeId
GROUP BY e.EmployeeId
HAVING
( select count(*) from ClientType ct
where ct.ClientType = 'A'
) >=
( select count(*) from ClientType ct
where ct.ClientType = 'B'
)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-03 : 18:14:16

Please provide a data example for your problem so that we can try it out on our machines. The data examples should show sample rows for all 3 of your tables and the sample data should be representative of your problem.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

liptonIcedTea
Starting Member

26 Posts

Posted - 2008-03-03 : 18:20:13

Ok... not sure how you want this to be...

but say there is an employee table

EmployeeId EmployName
1 Tim
2 Steve

and Client table with 3 columns (ClientType, EmployeeId, ClientId)
ClientType EmployeeId Client Id
A 1 1
A 1 2
A 2 3
B 2 4

And a reference table ClientType
ClientTypeId
A
B

What I want to do is write a query which returns all employees that have more ClientType A clients then ClientType B clients.

In the data example, Tim will be returned because he has 2 A clients and 0 B Clients whilst Steve has 1 A and 1 B.

Am I making any sense?

liptonIcedTea
Starting Member

26 Posts

Posted - 2008-03-03 : 18:50:08

basically, the following will compile and work, but the nested queries need like a WHERE statement where I can add the condition to only get the current employee, but becuase i can't seem to reference anything in the outer loop i can't seem to do that.

SELECT e.EmployeeId, COUNT(*)
FROM Employee e
INNER JOIN Clients c
ON e.EmployeeId = c.EmployeeId
inner join ClientType ct on ct.ClientTypeId = c.ClientTypeId
GROUP BY e.EmployeeId
HAVING
( select count(*) from ClientType ct
where ct.ClientType = 'A'
) >=
( select count(*) from ClientType ct
where ct.ClientType = 'B'
)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-03 : 18:59:30

[code]SELECT
E.*
FROM
@Employee AS E
INNER JOIN
@Client AS C
ON E.EmployeeID = C.EmployeeID
GROUP BY
E.EmployeeID,
E.EmployeeName
HAVING
SUM(CASE WHEN C.ClientType = 'A' THEN 1 ELSE 0 END) > SUM(CASE WHEN C.ClientType = 'B' THEN 1 ELSE 0 END)[/code]

liptonIcedTea
Starting Member

26 Posts

Posted - 2008-03-03 : 19:17:36

HAVING
SUM(CASE WHEN C.ClientType = 'A' THEN 1 ELSE 0 END) > SUM(CASE WHEN C.ClientType = 'B' THEN 1 ELSE 0 END)

this doesn't seem to work, it just returns the total number of clients, not the ones with A

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-03 : 19:22:54

quote:
Originally posted by liptonIcedTea

HAVING
SUM(CASE WHEN C.ClientType = 'A' THEN 1 ELSE 0 END) > SUM(CASE WHEN C.ClientType = 'B' THEN 1 ELSE 0 END)

this doesn't seem to work, it just returns the total number of clients, not the ones with A

It returns exactly what you specified when you showed us sample data as I was working on a solution too and then saw Lamprey's post. I tried his solution and saw it returned what you wanted (Tim) so I stopped working on the problem. If it isn't working for you, then you need to give us better sample data and show us exactly what it needs to return.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

liptonIcedTea
Starting Member

26 Posts

Posted - 2008-03-03 : 19:25:45

oh actually sorry it does work.

cheers

your a legend!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources