Simple select (I think) - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Simple select (I think)

Author

Topic

DavePruce
Starting Member

3 Posts

Posted - 2011-09-13 : 12:11:42

Hi All
Newbie so be gentle please...........
We have an Entity table (that holds records for EVERYTHING!) and an EntityRelationship (ER) table. ER has Parent and Child fields. Normally I can get around these 2 successfully, but I just cant get to grips with:
select all Companies (Entity type 9) that have 0 employees (entity type 5), so if anyone can give me a clue I would be very grateful!
Cheers
Dave

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 12:17:19

please give the table structures? entitytype is in which table? also show some sample data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DavePruce
Starting Member

3 Posts

Posted - 2011-09-13 : 12:24:57

Hi
The data is pretty simple:
Entity has entityid, entity type, and name and some other stuff (irrelevant here)
ER has ParentEntityID and Child EntityID and some other stuff
In England so off home now - back tomorrow
Cheers
dave

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 12:44:06

[code]select e.*
from ENtity e
OUTER APPLY (SELECT COUNT(1) AS Cnt FROM Entity e1
JOIN ER er
ON er.ChildEntityID = e1.EntityID
AND e1.EntityType=5
WHERE er.ParentEntityID = e.EntityID
)et
WHERE COALESCE(et.Cnt,0) =0
AND e.EntityType=9
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DavePruce
Starting Member

3 Posts

Posted - 2011-09-14 : 04:15:42

Thanks for the reply and the work!
Dave

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-14 : 08:24:30

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources