MySQL: Subtract one SELECT from another SELECT (with joins)

Ron1959
Ron1959 used Ask the Experts™
on
I have two somewhat complex SELECTS, the second of which is a subset of the first.  I need to build a query that will return only the records from the first SELECT which are not in the second.

That is, I want to subtract the second from the first.  I know I need to use AND NOT EXISTS and perhaps create temporary tables, but I'm just not getting the results I want.

FIRST SELECT QUERY
SELECT `contacts`.`idContacts` ,  `contacts`.`First_Name`, `contacts`.`Last_Name` , 
`Contacts_idContacts` , `Groups_idGroup`
FROM `libertytools`.`contacts` 
	JOIN `libertytools`.`groups` 
	JOIN `libertytools`.`groups_has_contacts` 
	ON Contacts_idContacts=idContacts 
	WHERE idGroup 
	IN (37) 
	AND Groups_idGroup 
	IN (37);

Open in new window


RESULTS:
+------------+------------+-----------+---------------------+----------------+
| idContacts | First_Name | Last_Name | Contacts_idContacts | Groups_idGroup |
+------------+------------+-----------+---------------------+----------------+
|          1 | Tom| Jones   |                   1 |             20 |
|          2 | Dick| Smith   |                   1 |             20 |
|          3 | Harry| Baker   |                   1 |             20 |
+------------+------------+-----------+---------------------+----------------+

Open in new window



SECOND SELECT QUERY
SELECT `contacts`.`idContacts` AS,  `contacts`.`First_Name`, `contacts`.`Last_Name` , 
`Contacts_idContacts` , `Groups_idGroup`
	FROM `libertytools`.`contacts` 
	JOIN `libertytools`.`groups` 
	JOIN `libertytools`.`groups_has_contacts` 
	ON Contacts_idContacts=idContacts 
	WHERE idGroup 
	IN (35) 
	AND Groups_idGroup 
	IN (35)

Open in new window


RESULTS
+------------+------------+-----------+---------------------+----------------+
| idContacts | First_Name | Last_Name | Contacts_idContacts | Groups_idGroup |
+------------+------------+-----------+---------------------+----------------+
|          2 | Dick| Smith   |                   1 |             20 |
+------------+------------+-----------+---------------------+----------------+

Open in new window



DESIRED RESULTS FROM SOLUTION
+------------+------------+-----------+---------------------+----------------+
| idContacts | First_Name | Last_Name | Contacts_idContacts | Groups_idGroup |
+------------+------------+-----------+---------------------+----------------+
|          1 | Tom| Jones   |                   1 |             20 |
|          3 | Harry| Baker   |                   1 |             20 |
+------------+------------+-----------+---------------------+----------------+

Open in new window


I would prefer to not have to use temp tables, but can if need be.

Thanks in advance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
Seems to me that all you need is a simple tweak of the WHERE clause:


SELECT `contacts`.`idContacts` ,  `contacts`.`First_Name`, `contacts`.`Last_Name` , 
`Contacts_idContacts` , `Groups_idGroup`
FROM `libertytools`.`contacts` 
	JOIN `libertytools`.`groups` 
	JOIN `libertytools`.`groups_has_contacts` 
	ON Contacts_idContacts=idContacts 
	WHERE idGroup IN (37) AND idGroup NOT IN (35)
	AND Groups_idGroup IN (37) AND Groups_idGroup NOT IN (35);

Open in new window

Author

Commented:
Thanks for the fast reply!

Your solution would be great because it's so simple, but it produces the same results as my first select. It does not subtract the second select.
Top Expert 2012

Commented:
>>Zones: MS SQL Server, SQL Query Syntax, MySQL Server<<
Has this question got anything to do with MS SQL Server?  Or were you needing a T-SQL solution, as well?
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Author

Commented:
Oops!  I meant to put it only in MySQL. I thought I changed it to MySQL....
Top Expert 2012

Commented:
No problem.  A Moderator can take care of that.
Top Expert 2011

Commented:
you want to do an except then

select .....
 from (query1
           EXCEPT
            query2 ) as x


like a UNION only it gives the rows in the first query but not in the second...


Top Expert 2011

Commented:
your queries aren't complete  only a partial on clause is present...

please post the correct queries

Author

Commented:
Lowfatspread, I am unable to get you EXCEPT query to work.  I think I am not getting the structure right.

Can you build an EXCEPT query with the example code I first posted.    I think it is complete, by the way.  I behaves as expected in my actual database.
Chief Technology Officer
Most Valuable Expert 2011
Commented:
Have you tried as your title suggests? i.e., NOT EXISTS (see code attachment)

By the way -- is this equivalent to what you are doing with the JOINs -- if so, might simplify things:
FROM `libertytools`.`contacts`
JOIN `libertytools`.`groups_has_contacts` ON Contacts_idContacts=idContacts
WHERE Groups_idGroup = 37

OR

FROM `libertytools`.`contacts`
JOIN `libertytools`.`groups_has_contacts` ON Contacts_idContacts=idContacts
JOIN `libertytools`.`groups` ON Groups_idGroup=idGroup
WHERE idGroup = 37
   

SELECT `contacts`.`idContacts` 
     , `contacts`.`First_Name`, `contacts`.`Last_Name` 
     , `Contacts_idContacts` , `Groups_idGroup`
FROM `libertytools`.`contacts` 
JOIN `libertytools`.`groups_has_contacts` 
   ON Contacts_idContacts=idContacts 
WHERE Groups_idGroup = 37
AND NOT EXISTS (
   SELECT 1
   FROM `libertytools`.`groups_has_contacts` gc2
   WHERE gc2.Groups_idGroup = 35
   AND gc2.Contacts_idContacts=idContacts
);

Open in new window

Author

Commented:
Your solution works perfectly!

One interesting anomaly, though, the Workbench query editor says it "fetched" three more records (out of 377) than it really did.

BTW: I had overly complicated my first query with the JOIN to the `groups` table only because I wanted to pull the name of the group out of that table.  It is probably more efficient to fetch the name in a separate query and make the query that does the most "crunching" as simple as possible.

One more question:  In the NOT EXISTS query, why the "SELECT 1"?

What is the "1" doing -- I haven't seen that in the reference.

Many thanks for the solution!
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
First, regarding group name, you can try the version below.

As far as the SELECT 1, you may also see SELECT NULL. The convention is addressing the fact that [NOT] EXISTS is concerned with the existence of a row. It is a short-circuiting technique, so actual data is unnecessary. However, if you bring back SELECT * for example, though minimal, there is overhead for bringing back data unnecessarily.
SELECT `contacts`.`idContacts` 
     , `contacts`.`First_Name`, `contacts`.`Last_Name` 
     , `Contacts_idContacts` , `Groups_idGroup`
FROM `libertytools`.`contacts` 
JOIN `libertytools`.`groups_has_contacts` 
   ON Contacts_idContacts=idContacts 
JOIN `libertytools`.`groups` ON Groups_idGroup=idGroup
WHERE Groups_idGroup = 37
AND NOT EXISTS (
   SELECT 1
   FROM `libertytools`.`groups_has_contacts` gc2
   WHERE gc2.Groups_idGroup = 35
   AND gc2.Contacts_idContacts=idContacts
);

Open in new window

Author

Commented:
You are clearly worthy of your "Genius" rating!

I'm also glad I renewed my EE subscription after being gone a few years.

Thanks!
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Thank you for the kind words. I am just glad we were able to help and that you have decided to once again rejoin the EE community.

Best regards and happy coding,
Kevin

Author

Commented:
Please be looking for a followup question.  I'm discovering more potential...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial