Solved

MySQL NOT IN Query

Posted on 2012-04-01
6
388 Views
Last Modified: 2012-06-27
I'm trying to get a list of names from one query that are not in the list of results from the second query:

Query 1 - Returns 289 users

SELECT tbl_Data.FirstName, tbl_Data.LastName, tbl_Data.ID
FROM tbl_Data
WHERE tbl_Data.CECMember =1

Open in new window


Query 2 - Returns 221 users

SELECT tbl_Data.FirstName, tbl_Data.LastName, tbl_Data.ID
FROM tbl_Data, tbl_Attendance
WHERE tbl_Data.ID = tbl_Attendance.VisitorID
AND tbl_Attendance.Date
BETWEEN  '2012-03-01'
AND  '2012-04-01'
AND (
tbl_Data.CECMember =  '1'
)
GROUP BY tbl_Attendance.VisitorID
ORDER BY  `tbl_Data`.`LastName` ASC 

Open in new window


Now, I'd like to get a list of the 68 users in Query 1 that are NOT in Query 2.

I tried using the following:

SELECT tbl_Data.FirstName, tbl_Data.LastName, tbl_Data.ID
FROM tbl_Data
WHERE tbl_Data.CECMember =1 and
tbl_Data.ID NOT IN
(
SELECT tbl_Data.FirstName, tbl_Data.LastName, tbl_Data.ID
FROM tbl_Data, tbl_Attendance
WHERE tbl_Data.ID = tbl_Attendance.VisitorID
AND tbl_Attendance.Date
BETWEEN  '2012-03-01'
AND  '2012-04-01'
AND
tbl_Data.CECMember =  '1'
GROUP BY tbl_Attendance.VisitorID
ORDER BY  `tbl_Data`.`LastName` ASC 
)

Open in new window


This gives me the following error message:  #1241 - Operand should contain 1 column(s)

Any help is greatly appreciated.
0
Comment
Question by:Jason92s
  • 3
  • 2
6 Comments
 
LVL 7

Expert Comment

by:micropc1
ID: 37793637
Try EXCEPT...

SELECT tbl_Data.FirstName, tbl_Data.LastName, tbl_Data.ID
FROM tbl_Data
WHERE tbl_Data.CECMember =1

EXCEPT

SELECT tbl_Data.FirstName, tbl_Data.LastName, tbl_Data.ID
FROM tbl_Data, tbl_Attendance
WHERE tbl_Data.ID = tbl_Attendance.VisitorID
AND tbl_Data.CECMember =  '1'
AND tbl_Attendance.Date BETWEEN  '2012-03-01' AND  '2012-04-01'

Open in new window

0
 

Author Comment

by:Jason92s
ID: 37793645
I don't believe MySQL has a "MINUS" function.
0
 
LVL 10

Expert Comment

by:Tony Barkdull
ID: 37793648
You should use the name of the query like

SELECT query1.FirstName, query1.LastName, query1.ID
FROM query1

LEFT JOIN query2 ON query2.ID

WHERE query.CECMember =1 AND query1.ID IS NOT NULL

Something similar to that
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 7

Accepted Solution

by:
micropc1 earned 500 total points
ID: 37793654
You're right it doesn't...MINUS is Oracle and for some reason I was thinking your post said MSSQL, which would use EXCEPT... so boy, I was way off...

yeah, with MySQL you'll need to use IN. You're sub-select can only return a single column..

Try...



SELECT tbl_Data.FirstName, tbl_Data.LastName, tbl_Data.ID
FROM tbl_Data
WHERE tbl_Data.CECMember =1 and
tbl_Data.ID NOT IN
(
SELECT tbl_Data.ID
FROM tbl_Data, tbl_Attendance
WHERE tbl_Data.ID = tbl_Attendance.VisitorID
AND tbl_Attendance.Date
BETWEEN  '2012-03-01'
AND  '2012-04-01'
AND
tbl_Data.CECMember =  '1'
GROUP BY tbl_Attendance.VisitorID
ORDER BY  `tbl_Data`.`LastName` ASC 
)

Open in new window

0
 

Author Comment

by:Jason92s
ID: 37793671
That's it!  Man, I complete overlooked that.  Thank you so much!
0
 

Author Closing Comment

by:Jason92s
ID: 37793672
Very helpful, thank you!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

20 Experts available now in Live!

Get 1:1 Help Now