where id is not in a set? - 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

General SQL Server Forums

New to SQL Server Programming

where id is not in a set?

Author

Topic

carleton
Starting Member

3 Posts

Posted - 2009-03-14 : 10:15:49

I have four tables:

Client: The client
License: The license I gave a client, either a demo license or regular license
UnlockKey: This is the unlock key for the license, as time go on and people renew maintenance, they will have multiple unlock keys per license
UnlockKeyType: Standard type table to determine the type of unlock key, demo, bronze, silver, or gold

The License has a FK to Client and UnlockKey has a FK to License & UnlockKeyType.

EVERY client has a one or more entries for a demo License/UnlockKey, only customers have an additional row(s) for their bronze, silver, or gold License/UnlockKey.

My goal is to get all the clients that ONLY have demo licenses.

I do have a Customer view that shows me everyone that has a UnlockKeyType that is NOT a demo and I have a view of ALL client licenses.

I am thinking something like this:

SELECT * FROM AllClientLicenses
WHERE clientID != [SELECT clientID FROM CustomerView]

How would I do that in SQL Server 2008? Is there a better way of doing this? I do have complete control over the data so I can restructure it too.

Carleton

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-14 : 10:22:52

SELECT cl.* FROM AllClientLicenses CL
  Left outer join CustomerView CV
 on CL.ClientID = CV.clientID
Where CV.ClientID is NULL

carleton
Starting Member

3 Posts

Posted - 2009-03-14 : 11:29:06

That worked out perfectly, thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources