Solved

MySQL NOT IN Query

Posted on 2012-04-01
6
397 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
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.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

820 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