Dahker Furm Dahker Furm - 7 months ago 14
SQL Question

How to exclude matching records in a MySQL query?

I'm trying to get a list of customers waiting on service and idle agents based on the following tables:

Customers Table
|Customers_ID||Customer Name|
|1 ||John |
|2 ||Sam |
|3 ||Kuji |

Agents Table
|AGENT_ID||AGENT Name|
|99 ||Kelly |
|98 ||Raji |
|97 ||Mertle |

Service Table (Customers being served by Agents)
|QUE_ID||AGENT Name||Customer|
|1001 ||Kelly ||Kuji |
|1002 ||Raji ||Sam |

SELECT Customer.custname

FROM Customer LEFT JOIN Service ON ( Customer.custname = Service.custname)

UNION

SELECT Agent.agentname

FROM Agent RIGHT JOIN Service ON ( Agent.agentname = Service.agentname)

WHERE
(Agent.agentname = service.agentname) IS NULL

GROUP BY Customer.custname";


This is returning all the values in the customer table and not the customers without agents. How can I exclude the records that already have a match?

Answer

You can use NOT EXISTS to check for agents and customers that are not yet in Service table:

SELECT custname
FROM Customer c
WHERE NOT EXISTS(
    SELECT 1
    FROM Service s
    WHERE s.custname = c.custname
)

UNION ALL

SELECT agentname
FROM Agent a
WHERE NOT EXISTS(
    SELECT 1
    FROM Service s
    WHERE s.agentname = a.agentname
)