Solved

MySQL NOT IN Query

Posted on 2012-04-01
6
399 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
[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
  • 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

724 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