Select row if col1 value exits twice and col2 is not null

We are monitoring network devices. A device may appear at more than one switch.

We want to filter out those devices which are on an uplink / portchannel in case it appears also on another port. All other devices are selected.

Let's say the table looks like:

HOST,  SWITCH,  PORT
HostA, Switch1, 01
HostB, Switch1, 02
HostA, Switch2, Po  - Po is portchannel / uplink
HostC, Switch2, Po  - Po is portchannel / uplink

Desired Output:

HostA, Switch1, 01
HostB, Switch1, 02
HostC, Swtich2, Po  - is only on an uplink / so that is OK

The Entry HostA, Switch2, Po needs to be filtered out since it appears on another port as well.

Now the question is how to write an efficient query.

In SQL terms we want to select all rows except those where HOST appears twice. Then we want only that row where PORT is not 'Po'

Our current query is slow because of subqueries !? I assume that the subquery is creating a cartesian product - right?

SELECT * FROM devices t1
WHERE NOT ((Port = 'Po') AND 
      ((Select count(*) from table t2 where t1.host=t2.host AND NOT Port='Po') > 0))

Again the question is how to write a faster SQL query??

Answers


Not exists should be faster than correlated count(*)

select *
  from devices t1
 where (port <> 'Po'
        or not exists (select null
                         from devices t2
                        where t2.host = t1.host
                          and t2.Port <> 'Po')
       )

UPDATE: if you think that join version would perform better:

select t1.*
  from devices t1
  left join devices t2
    on t1.host = t2.host
   and t2.port <> 'Po'
 where (t1.port <> 'Po'
    or t2.port is null)

The query self-joines devices table, removing Po's from second instance. It then proceeds to select all non-Po's and Po's without matching non-Po. Oh well, what an explanation.


SELECT HOST as HOST,  SWITCH,  PORT from table 
WHERE port<>'po' 
UNION ALL
SELECT MAX(HOST) as HOST,  SWITCH,  PORT from table
WHERE port='po'
GROUP BY SWITCH,  PORT 

Assuming there is at most one port on a host/switch combination, you can use group by:

select HOST, SWITCH,
       (case when max(case when PORT <> 'Po' then 1 else 0 end) = 1
             then max(case when PORT <> 'Po' then port end)
             else 'Po'
        end) port
from t
group by host, switch

If you have a database that supports windows functions, you can use:

select t.host, t.switch, t.port
from (select t.*,
             sum(isPo) over (partition by host, switch) as numPo,
             count(*) over (partition by host, switch) as numAll
      from (select t.*,
                   (case when port = 'po' then 1 else 0 end) as isPo
            from t
           ) t
     ) t
where numPo = numAll or -- all are Po
      (port <> 'Po')    -- remove any other Pos

Need Your Help

client-MVC vs server-MVC

model-view-controller client-side server-side

I am looking to get some input from other users on the benefits of server-side MVC. With the power of many javascript libraries. What good purpose does server-side MVC server anymore?

Xpath - How to get the data contained between elements, not the elements themselves

java html xhtml xpath

I'm writing a Java program that scrapes a web page for links and then stores them in a database. I'm having problems though. Using HTMLUnit, I wrote the following: