select with exceptional case count = 0 and registers = 1

Question

Works fine:

SELECT  m.*, i.description
FROM mac_address_gt m
INNER JOIN int_gt i ON m.ip = i.ip AND m.interface = i.interface
WHERE m.mac = ? AND m.count > 0 AND m.vlan = ?
ORDER BY m.count asc

But there is one exceptional case, where if the m.count = 0 and the number of returned registers = 1, I must show it.

Answer 1

At this point it is unclear what the number of registers is in your case, so this answer will only focus on how the query should be changed. A possible resulting query could look like

SELECT  m.*, i.description
FROM mac_address_gt m
INNER JOIN int_gt i ON m.ip = i.ip AND m.interface = i.interface
WHERE m.mac = ? AND ((m.count > 0) OR ((m.count) AND (<number of returned registers criteria>))) AND m.vlan = ?
ORDER BY m.count asc

The <number of returned registers criteria> is up to you to implement. Also, if the number of returned registers happens to be m.vlan or m.mac, then you will need to restructure your query.

EDIT

In light of the query given in the comment section this is how the query can be rewritten:

SELECT m.*, i.description 
FROM mac_address_hn m 
INNER JOIN int_hn i 
ON m.ip = i.ip AND 
   m.interface = i.interface 
JOIN (SELECT COUNT(m.count) as cnt FROM mac_address_hn m INNER JOIN int_hn i ON m.ip = i.ip AND m.interface = i.interface WHERE m.mac = "78d3.47a3.5b78" AND m.vlan = "4010") mycount
ON 1=1
WHERE  m.mac = "78d3.47a3.5b78" AND 
       m.vlan = "4010" AND 
       (((count = 0) and (mycount.cnt = 1)) or
        ((count > 0) and (mycount.cnt > 0))
       );