Find same values in 2 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

SQL Server 2000 Forums

Transact-SQL (2000)

Find same values in 2 statements

Author

Topic

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2012-05-29 : 10:29:13

Hi,

I have the below TSQL statement in SQL 2000.

The first select returns 1 row which has a box no, a shelf no and an area no.
The second select can return multiple rows each with a box no, a shelf no and a row no.

I want to only return a yes if the first select matches exactly a row in the second select i.e. they have the same area no and the same shelf no and the same box no.

I have tried IN and the WHERE EXISTS but nothing seems to work as i expect. In my below example I was trying to use IN but it doesnt work.

select area, shelf, box from WS_Storage_File where id=24
AND IN
(SELECT a.area, a.shelf, a.box FROM WS_Storage_FileStatus a WHERE a.status = 'checkedout')

Thanks for any help...

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-05-29 : 11:05:42

select 'yes'
where exists
(
select *
from WS_Storage_File a
join WS_Storage_FileStatus b
on a.area = b.area
and a.shelf = b.shelf
and a.box = b.box
and b.status = 'checkedout'
and a.id=24
)

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

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2012-05-29 : 11:55:15

thanks for that...that got it...i ended up confusing myself...ur way is a much better way of doin it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources