Solved

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

Posted on 2011-09-03
15
460 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 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

24 Experts available now in Live!

Get 1:1 Help Now