CODEWITHSUNDEEP
mysqlsql
dyao
dyao

Reputation: 1011

Filtering a specific value in a row

Sorry if its hard for me to show it in writing, but I can definitely show it.

So lets say I have this table:

(`name` varchar(5), `value_name` varchar(18))
;

INSERT INTO Alarms
(`name`, `value_name`)

VALUES
('cws-1', 'linkupdown'),
('cws-1', 'linkupdown'),
('cws-1', 'performancedegrade'),
('cws-1', 'performancedegrade'),
('cws-2', 'ICMP-Ping'),
('cws-2', 'linkupdown'),
('cws-3', 'performancedegrade')
;

What the table looks like:

name      value_name
-----     ----------
cws-1     linkupdown
cws-1     linkupdown
cws-1     performancedegrade
cws-1     performancedegrade
cws-2     ICMP-Ping
cws-2     linkupdown
cws-3     performancedegrade

I want a query that excludes any records that have a 'linkupdown' in the value_name column where name = 'cws-1'

So the result would be:

name      value_name
-----     ----------
cws-1     performancedegrade
cws-1     performancedegrade
cws-2     ICMP-Ping
cws-2     linkupdown
cws-3     performancedegrade

It seems really simple but I can't seem to come up with a query that satisfy this result.

Any help is appreciated.

Upvotes: 0

Views: 34

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use NOT EXISTS:

SELECT name, value_name
FROM Alarms AS a
WHERE name = 'cws-1' 
      AND NOT EXISTS (SELECT 1
                      FROM Alarms
                      WHERE a.name = name AND value_name =  'linkupdown')

The above query return all cws-1 named records that don't have any relation to records with value_name='linkupdown'.

Taking a closer look at the desired output as stated in the OP, this might be what you really want:

SELECT name, value_name
FROM Alarms AS a
WHERE NOT (name = 'cws-1' AND value_name = 'linkupdown')

This query will select all records from Alarms excluding any records that have name = 'cws-1' and value_name='linkupdown'.

SQL Fiddle Demo

Upvotes: 1

Related Questions