Querying something that is not there

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)

Querying something that is not there

Author

Topic

groston
Yak Posting Veteran

61 Posts

Posted - 2009-10-21 : 00:26:54

Given a database with two tables:

TABLE Items:
  ItemID integer,
  Description varchar,
  ItemTypeID integer

and

TABLE ItemTypes:
  ItemTypeID integer,
  Description varchar,
  Available integer

The basic idea is that there can be no more Items of a particular ItemTypeID than Available.

I would like to write a query which returns all of the records from the ItemTypes table for which the number of Items if less than Available.

Let me give an example:

Items:
1, golf ball, 1
2, base ball, 1
3, basket ball, 1
4, pencil, 2

ItemTypes:
1, spheres, 3
2, cylinders, 4

The query would return:
2, cylinders, 4
since there are already three spheres in the Items table.

Thank you for your assistance!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-21 : 00:48:32

[code]
select *
from ItemTypes t
where not exists
(
select ItemTypeID
from Items
group by ItemTypeID
having count(*) < t.Available
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-21 : 00:52:01

select
T.*
from
ItemTypes T,
(Select ItemTypeID,count(1) as Available From Items Group By ItemTypeID)I
Where
I.ItemTypeID=T.ItemTypeID AND
T.Available >I.Available

groston
Yak Posting Veteran

61 Posts

Posted - 2009-10-21 : 15:00:51

khtan - your reply put me on the right track - thank you. Instead of 'not exists', I used 'not in'. I also changed the '<' to '>=". It works!

Sanoj - I don't think that your approach works because if there is no Item of ItemType, then the record for that ItemType is not retrieved. However, your use of a query as a 'table' was a learning experience. Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources