asked on 2/9/2016

Find customers who ordered in 2015 but not 2016

I have a table called Passes1 that shows every ticket sale we have ever made.
I want to run a query on customers that bought a ticket in 2015 but have not bought yet in 2016

I have this query -
SELECT * FROM Passes1 as pa WHERE NOT EXISTS (Select * from Passes1 as pa1 WHERE pa.THID=pa1.THID AND pa1.Year = '2016') AND Year ='2015'

Where am I off?

SQL

Last Comment

Olaf Doschke

2/10/2016

Éric Moreau

2/9/2016

or you do a LEFT JOIN:

select *
from Passes1 as P2015
left join Passes as P2016
on P2016.Year = '2016'
and P2016.THID = P2015.THID
where P2015.Year = '2015'
and P2016.THID is null

Open in new window

ASKER CERTIFIED SOLUTION

Olaf Doschke

2/10/2016

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Olaf Doschke

2/10/2016

...more likely than a Year column you would have an orderdate column and would pick MAX(YEAR(orderdate)) = 2015

Bye, Olaf.

SQL

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

61K

Questions

Followers

Top Experts

Get a personalized solution from industry experts

Ask the experts

"Invaluable tool for our organization"
Josh Regalski
"This is the best money I have ever spent."
@rwheeler23
"Your help has saved me hundreds of hours of internet surfing."
@fblack61
"Just a dang good service!"
@golferski
"Worth every penny."
Steve Hougom
"It’s been a life saver."
Maria Halt
"Best support site I’ve seen!"
Bob Schneider
"I would be lost without them."
@fblack61
"Saved my job multiple times."
Walt Forbes
"I love this site."
Maria Duwe
"Friendly respectful help."
Andrew Kowtalo
"Such top-notch experts."
@magento
"The best money I have ever spent."
@rwheeler23
"Beyond any comprehensible value"
George Morris
"Invaluable tool for our organization"
Josh Regalski
"This is the best money I have ever spent."
@rwheeler23

Read over 600 more reviews

TRUSTED BY