Warning: Undefined array key "action" in /home/bakers/html/entryreply.php on line 5

Warning: Undefined variable $output in /home/bakers/html/entryreply.php on line 50

Warning: Undefined array key "EntrySubject" in /home/bakers/html/entryreply.php on line 51

Warning: Undefined variable $logintext in /home/bakers/html/entryreply.php on line 97

Warning: Undefined variable $reply_name in /home/bakers/html/entryreply.php on line 103

Warning: Undefined variable $reply_email in /home/bakers/html/entryreply.php on line 104
Perturb.org - interesting technology related things from around the internet

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;


Note: Replies will be formatted with PHP Markdown Extra syntax.

Name: Email (Not Required):
 
Logged IP: 18.212.120.195
To prevent spam please submit by clicking the kitten: