Solved

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

Posted on 2011-09-03
15
459 Views
Last Modified: 2012-05-12
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!
0
Comment
Question by:Ron1959
  • 6
  • 3
  • 2
  • +2
15 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36477878
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

0
 

Author Comment

by:Ron1959
ID: 36477894
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36478121
>>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?
0
 

Author Comment

by:Ron1959
ID: 36478152
Oops!  I meant to put it only in MySQL. I thought I changed it to MySQL....
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36478162
No problem.  A Moderator can take care of that.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36478719
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...


0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36478735
your queries aren't complete  only a partial on clause is present...

please post the correct queries
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:Ron1959
ID: 36479408
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.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 125 total points
ID: 36479613
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

0
 

Author Closing Comment

by:Ron1959
ID: 36480235
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!
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36480604
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

0
 

Author Comment

by:Ron1959
ID: 36481672
You are clearly worthy of your "Genius" rating!

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

Thanks!
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36481723
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
0
 

Author Comment

by:Ron1959
ID: 36481736
Please be looking for a followup question.  I'm discovering more potential...
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Creating and Managing Databases with phpMyAdmin in cPanel.
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now