Solved

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

Posted on 2011-09-03
15
467 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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
 

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 60

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 60

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 60

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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

717 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