Solved

Simple SQL Query

Posted on 2013-01-21
19
319 Views
Last Modified: 2013-01-29
I have a very simple request but for some reason I can not get my mind around how to do it. I have two tables. ORDER and CUSTMAST. I need to see the customers that did loads in 2011 but not in 2012. The customer in ORDER is ORCUST and the customer code in CUSTMAST is CUCODE.

See simple enough. Help please
0
Comment
Question by:JArndt42
[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
  • 6
  • 6
  • 4
  • +1
19 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 250 total points
ID: 38801997
select * from custmas
where customer_code in (select customer_code from order where load_year = 2011)
and customer_code not in (select customer_code from order where load_year = 2012)
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 38802025
First of all, calling a table 'ORDER' is a monumentally bad idea. It is a keyword in SQL and is simply going to cause you a problem.

But, changing it to ORDERS or ORDMAST (keeping the convention you have with CUSTMAST - convention is useful!), then adapt this
SELECT custmast.*, ordmast.*
FROM custmast INNER JOIN ordmast ON custmast.cucode = ordmast.orcust
WHERE year(ordmast.date) = 2011

Open in new window


See http://webcheatsheet.com/SQL/access_functions/year.php for more info on the YEAR() function.
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 38802049
Oh. Sorry. Having just seen sdstuber's answer. Yeah. Much better.

You MAY get some performance increase if you limit the number of rows in the sub queries to just 1 row. You don't need ALL the 2011 or 2012 ordes, just 1.

select * from custmas
where customer_code in (select top 1 customer_code from order where load_year = 2011)
and customer_code not in (select top 1 customer_code from order where load_year = 2012)

Open in new window


But with the same potential issue of the table name being 'ORDER'
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 1

Author Comment

by:JArndt42
ID: 38802090
RQ,
I am not able to change the table names. this is a pass through query to an AS400 back end. I understand the naming conventions but the database developer that did the program on the back end did not or didn't care. I do many queries with that table and have yet had any issues. This is what it looks like per your request and it returns customers with orders in 2012 as well as customers in 2011. ?????
SELECT CUCODE,CUNAME,DATE(SUBSTR(DIGITS(000000+CULACT),2,7)) AS LASTLOAD
FROM CUSTMAST
WHERE CUCODE IN (SELECT ORCUST FROM ORDER WHERE ORPDAT BETWEEN 2011001 AND 2011365 AND ORSTAT <>'C')
AND CUCODE NOT IN(SELECT ORCUST FROM ORDER WHERE ORPDAT BETWEEN 2012001 AND 2012365 AND ORSTAT <>'C')

Open in new window

0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 38802121
If you run the 2 sub queries independently, do they produce the correct results?

Add the ORPDAT column the the output of the subqueries temporarily to make sure that they are being accessed correctly.

And a SELECT DISTINCT may be more efficient (not TOP 1 - what was I thinking!!!)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38802123
you have an additional   criteria in your subqueries.

if you have ordcust 1234,  2011001  with ordstat 'A'
and ordcust 1234, 2012001 with ordstat 'C'


it'll show up in your result set
0
 
LVL 1

Author Comment

by:JArndt42
ID: 38802151
Very slow query. I used distinct in the subs. I am thinking about running the two queries that return a single set of records. One for 2011 and another query for 2012. Then do an ACCESS query to get the results. I wanted it in just one pass through though. the query in plain English is as follows

Show me the customer code, customer name and the date of the last load
from the customer master
where the customer had orders in 2011 and the order was not canceled
and the customer did not have orders in 2012 and the order was not canceled.
0
 
LVL 40

Assisted Solution

by:Richard Quadling
Richard Quadling earned 250 total points
ID: 38802350
If you can get the 2 sub queries to return the correct values, then the IN(2011 query) AND NOT IN(2012 query) should be OK.
0
 
LVL 10

Expert Comment

by:Luke Chung
ID: 38805775
Take a look at my paper on creating outer join queries: http://www.fmsinc.com/MicrosoftAccess/query/outer-join/index.html

You'll want to create a query that has all the people who purchased in 2012. Then you want to  link to it from your main table where you filter for 2011 customers who are NOT IN the 2012 list.
0
 
LVL 1

Author Comment

by:JArndt42
ID: 38829284
It worked. Thank you.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38829575
Isn't the accepted answer just a rephrasing of the original post?
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 38830116
I think a split with sdstuber would be a better option here.

And just as an aside, as 2012 was a leap year, should the end date be 2012366?
0
 
LVL 1

Author Comment

by:JArndt42
ID: 38831406
Actually I have no idea on how to do that. instructions please.
0
 
LVL 1

Author Comment

by:JArndt42
ID: 38831473
Yes please do split the points. You were both helpful. Thank you very much.
0
 
LVL 1

Author Closing Comment

by:JArndt42
ID: 38831553
thank you both very much for your help.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38831565
glad we could help, and thank you !
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 38832557
Yep! Glad to have helped.

And JArndt42, in the future, if more than one expert has helped provide you with enough information to get your answer, then you can choose to award points to multiple solutions.
0

Featured Post

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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: …

695 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