Solved

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

Posted on 2011-09-03
15
461 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

776 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