Conditional Select... (case, union, subselect?) -- sql server programming

mwhitis NO[at]SPAM fuse.net
3/23/2004 7:56:13 AM

Hugo,

Thanks for the reply. I've changed the like to = as you suggested. I
had gotten in the habit of using like because most of my queries are
wildcards.

These queries aren't quite what I'm looking for, though. When I run
them, they return a single YES or NO. Presumably, depdending on which
system it gets first from the System table. The output I need is list
of each system name, with it's status.. Like:

System_DATA.Name0|IsInstalled
MAILSERVER |YES
DATASERVER |YES
DATASERVER2 |NO

ETC..

Thanks,

Mike

[quoted text, click to view]

Hugo Kornelis
3/23/2004 9:46:27 AM

[quoted text, click to view]

First, you should replace "LIKE 'foo.exe'" with "= 'foo.exe'". Use
LIKE only for wildcard searches.

What you want can be achieved by:

IF EXISTS
(SELECT System_Data.Name0,SoftwareFile.FileName
FROM System_Data
INNER JOIN SoftwareInventory on
System_Data.MachineID=SoftwareInventory.ClientID
INNER JOIN SoftwareFile on
SoftwareInventory.FileID-SoftwareFile.FileID
WHERE System_DATA.Name0 like '%SERVER%
and SoftwareFile.FileName = 'foo.exe')
PRINT 'YES'
ELSE PRINT 'NO'

Or, if you must have it in a select statement:

SELECT CASE
WHEN EXISTS
(SELECT System_Data.Name0,SoftwareFile.FileName
FROM System_Data
INNER JOIN SoftwareInventory on
System_Data.MachineID=SoftwareInventory.ClientID
INNER JOIN SoftwareFile on
SoftwareInventory.FileID-SoftwareFile.FileID
WHERE System_DATA.Name0 like '%SERVER%
and SoftwareFile.FileName = 'foo.exe')
THEN 'YES'
ELSE 'NO'
END

Best, Hugo
--

Hugo Kornelis
3/23/2004 6:01:50 PM

[quoted text, click to view]

In that case, you need:

SELECT System_Data.Name0,
CASE
WHEN EXISTS
(SELECT *
FROM SoftwareInventory
INNER JOIN SoftwareFile on
SoftwareInventory.FileID = SoftwareFile.FileID
WHERE SoftwareInventory.ClientID = System_Data.MachineID
and SoftwareFile.FileName = 'foo.exe')
THEN 'YES'
ELSE 'NO'
END AS IsInstalled
FROM SystemData
WHERE System_Data.Name0 like '%SERVER%'

(untested)

Best, Hugo
--

mwhitis NO[at]SPAM fuse.net
3/24/2004 6:52:17 AM

Thanks Hugo. Once I looked at your code, I realized I was making it
FAR too complicated on myself. It's really just a simple subselect.
Once that clicked into place, I was a little embarassed that I didn't
see it before.

Thanks again

Mike

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Conditional Select... (case, union, subselect?)