Help with value

Can someone please tell me the best way to handle this problem...
I have the following
SERVICES
Server Service
-- --
S1 A,B,C,D,E,F
S2 A,B,C,D,E,F
S3 A,C,D,E,F
I need to run a query that will select a Server and Services like A,B,
or C. If however, like Server S3, there is no B, then replace it or
somehow state that B is not installed.
Can SQL do this or do I need to pull the information and use something
else?
I can get it if all Services are there, however, if 1 is missing, then
I get messed up.
ex. SELECT a.Server,a.Service FROM SERVICES a
WHERE (a.Service like '%A%' or a.Service like '%B%' or a.Service
like '%C%' ) and
(a.Server = 'S1')
Thanksd4 wrote:
> Can someone please tell me the best way to handle this problem...
> I have the following
> SERVICES
> Server Service
> -- --
> S1 A,B,C,D,E,F
> S2 A,B,C,D,E,F
> S3 A,C,D,E,F
>
The best way is to fix the daft design. Like:
CREATE TABLE server_services (server CHAR(2) NOT NULL REFERENCES
servers (server), service CHAR(1) NOT NULL REFERENCES services
(service), CONSTRAINT pk_server_services PRIMARY KEY (server, service))
;
INSERT INTO server_services (server, service)
SELECT 'S1','A' UNION ALL
SELECT 'S1','B' UNION ALL
SELECT 'S1','C' ... etc
SELECT S.server, T.service,
CASE WHEN V.server IS NOT NULL
THEN 'Installed'
ELSE 'Not Installed'
END is_installed
FROM server AS S
CROSS JOIN services AS T
LEFT JOIN server_services AS V
ON V.server = S.server
AND V.service = T.service ;
as so often in SQL, your problems will disappear if you get the design
right to start with.
David Portas
SQL Server MVP
--|||I'm just pulling the data, didn't get to design it. And really the
Services are services that are running (or stopped,etc) on that server,
so they may be different for each one. Thanks for the reply, I'll try
it out.|||I'm just pulling the data, didn't get to design it. And really the
Services are services that are running (or stopped,etc) on that server,
so they may be different for each one. Thanks for the reply, I'll try
it out.