MySQL Complicated subquery joins

We needed to do two complicated (group by/having) queries, and find the common CustIDs between them. Easy way is to create a temporary table with the CustIDs from the first table, and then do an INNER JOIN against that temporary table on the select for the second table.

DROP TABLE IF EXISTS CustID;
CREATE TEMPORARY TABLE CustID (CustID Integer);

INSERT INTO CustID (CustID)
   SELECT CustID
   FROM ViewCircuitServices
   WHERE SvcID = 3 AND CircuitType = 'dsl'
   GROUP BY CustID
   HAVING count(SvcID) >= 2;

SELECT CustID, SvcID
FROM ViewCircuitServices
INNER JOIN CustID USING (CustID)
WHERE SvcID IN (2,9,25) AND CircuitType = 'dsl'
GROUP BY CustID;
Leave A Reply
All content licensed under the Creative Commons License