Solved

Help with query against AdventureWorks database - Customers

Posted on 2010-08-28
16
854 Views
Last Modified: 2012-05-10
Need a hand with this query:

Return the Customer ID, First Name, and Last Name of those individuals in the Customer table who have made no Sales purchases.

Thanks!
0
Comment
Question by:John500
  • 6
  • 4
  • 2
  • +1
16 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33551320
John500,

Again, seems you are trying to learn SQL, so here is a hint. You can use NOT EXISTS or LEFT JOIN to sales as shown in other question and simply look for where the value from the joined table is NULL, meaning no match.

e.g.,

select id, fname, lname
from customers c
where not exists (
   select *
   from {your sales table and joins}
   where c.id = {sales query customer id}
);

or

select c.id, c.fname, c.lname
from customers c
left join ({your sales query}) sls
   on c.id = sls.{sales query customer id}
where sls.{sales query customer id} is null;

Hope that helps.

Post back with your attempt against AdventureWorks if you have trouble noting any errors you are getting from parser.  If parses, but you don't get the results you expect, then let us know what you envision the data results should look like and we can tailor our assistance to meet your needs.
0
 

Author Comment

by:John500
ID: 33551371
Yes, again appreciate that.  I'm not familar with the AdventureWorks database.  Thus, the feed back will speed this up for me.

For anybody else interested, the most recent database version is here:

http://msftdbprodsamples.codeplex.com/releases/view/4004

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33551382
Folks writing code here for you won't familiarize you with the AdventureWorks any better than the documentation online will or simply looking around at the tables in SQL Management Studio.  Furthermore, us writing code for you here will NOT help you to pass any SQL courses or examinations you may be aspiring to.  If you want to learn SQL syntax, then please take the advice given and try to apply the concept yourself so you can actually learn.
0
 

Author Comment

by:John500
ID: 33554143
>>  seems you are trying to learn SQL

Nope, that's not the case.  I'm not familar with the AdventureWorks database and I'm trying to finish these questions by tomorrow given a time crunch.  The more thoughts the better on these things.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33554168
Finish what questions?  If this is homework, we cannot give you the answer directly, which is why I am taking this approach; therefore, you need to provide some context why getting these examples against the AdventureWorks database would be a time crunch but not being used to learn SQL.
0
 

Author Comment

by:John500
ID: 33554226
Don't worry about it.  The best example I could give you is like that other question I posted where you referrenced 'ca.addressType='Main Office' as the way to identify customers to be deleted.

This is just about collabortion as is with any other question we post around here.  The time crunch I mentioned above is not related to moral issues.

As I say I don't have time for it so don't worry about any of these question...
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:John500
ID: 33554232
when I say don't worry about any of these questions, I'm saying don't worry about participation, just bug out per say....
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33554243
cyberwiki suggested that code, firstly, but understand I am not trying to be difficult here.  My first post has explained everything you should need to know but you are indicating it is not helpful because it is not written against the specific tables of the AdventureWorks database which you have provided links for in other questions so you clearly have it in front of you or know how to get it.  Therefore, my difficulty is if you really took the time to apply what I said, it should have been simple or would not have been unreasonable for you to post back the code you tried and the error you were getting as I asked.

It is not our responsibility here to do anyone's work for them.

We are trying to help -- me writing the code for you won't help you understand the AdventureWorks any better just will display my understanding -- how is that more helpful than my post above that explains the syntax ?
0
 

Author Comment

by:John500
ID: 33554274
Look, it's like this, the more eyes the more angles.  It's that simple!  This could go on for every with you.  I'm not interested in that.  Go find a pulpit if that's your deal.  This is the house of collabloration here.  The house of collaboration!  The input I recieve here is for review and consideration.  At the right time I sit down and go over the thoughts I've gained from others.  That might be tonight at 12:00.  Does that make sense?

If you have personal issues then the best thing to do is bug out... not to start adding all this extra crap to the question posted.  Just bug out.
0
 

Author Comment

by:John500
ID: 33554533
Back in 2007 I posted a question that went like this:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22747991.html

Noticed the experts answered and didn't mentioned a thing about where this question was coming from.  It was a questioned to be answered by anyone interested and to give as much as they desired to give.  Some a little, some a lot.

If you had looked at my history of questions, that should have told you something about my general knowledge.  My response above said, "Nope, that's not the case.  I'm not familar with the AdventureWorks database ..."  This should have been enough.  Subject dropped and over with!!!

Nobody has to be put on the witness stand around here.  Experts who aren't interested just don't respond.  If they don't like what they think they are seeing, they just don't respond because this isn't a place to perform judgements on people.  If you aren't interested, you stay away.

Did I have a list of questions regarding the AdventureWorks database, yes!  Did I need to be questioned or put on notice about how we should proceed - no!  A thousand times no.

Regarding the "bug out" statement, unprofessional? What ?  If I were at a meeting and I stood up and said, "folks, I need to bug out"  are you tell me they would have all said, "oh no, what unprofessionalism is this?"

I'll take this up with the EE staff
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33557569
Looking at http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_26108045.html
I tend to believe your level of proficiency and that you are only looking to understand the relationships in the Adventureworks db.

Moderators: Based on the link I gave above, and inspection of John's profile I believe there is some misunderstanding here.  Delete my comment if you feel it doesn't belong.


select CustomerID, FirstName, LastName
from sales.Customer c
where not exists (
      select *
      from sales.SalesOrderHeader h
      where h.CustomerID=c.CustomerID)
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 33559112
Well, there has been some conjecture as to "homework" or not.

And the member rules about "academic honesty" do prevent us from posting the answer - however - we are here to help the learning process and the understanding.

I think the conjecture stems from the use of the Adventureworks database - if trying to solve a real life problem, then we tend to see real life (or even masked) data structures. If the Adventureworks database is the big mystery, then it seems we have two mysteries - first how to unravel the Adventureworks database and secondly how to identify data from one table that has no activity in another.

In terms of proficiency as cyberkiwi says above, well, this type of SQL is not uncommon and as mwvisa1 said at the beginning it is usually resolved via not exists or a join (or other methods). Then there is the comment about "crunch time".

John500, you do need to be a bit patient with us, we do see a lot of questions and sometimes things appear / percieved to be other than originally intended, and albeit rarely, we do get that perception wrong from time to time. Your question is no exception to those perceptions, and if you read back, you might even see why...

So, is the problem understanding the data relationships or understanding how to identify the exceptions ?

If it is the database, then I can strongly recommend looking at the data dictionary : http://msdn.microsoft.com/en-us/library/ms124438(SQL.100).aspx and the Server Objects : http://msdn.microsoft.com/en-us/library/ms124425(v=SQL.100).aspx and most importantly, check some of the "scenarios" which does explain how some of those tables link together http://msdn.microsoft.com/en-us/library/ms124824(SQL.100).aspx

If it is the code, then there are a couple of methods as outlined by mwvisa1 in the very first post, and one of which detailed by cyberkiwi albeit missing some data sources (ie customers can be individuals or stores, and if talking individuals then names are in person.contact table).

While I know Adventureworks is the sample database, it is pretty horrible to learn simple concepts from because you first have to learn how the data hangs together.

So, because you have mentioned "individuals" can we assume you do not want customers that are stores ? The links are different, so, will be easier for now to assume individuals (see that last link above).

And to get something as seemingly simple as customerID, firstname, lastname, it is a bit convoluted e.g.

SELECT Cu.customerID, FirstName, LastName
FROM Person.Contact AS C
JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
JOIN Sales.Customer AS Cu ON I.CustomerID = Cu.CustomerID
WHERE Cu.CustomerType = 'I'


So, if we now want just those individuals without any salesorderheaders, we can now add in the "NOT EXISTS" which is a fairly typical way of running this type of query...

SELECT Cu.customerID, FirstName, LastName
FROM Person.Contact AS C
JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
JOIN Sales.Customer AS Cu ON I.CustomerID = Cu.CustomerID
WHERE Cu.CustomerType = 'I'
AND NOT EXISTS (select * from sales.SalesOrderHeader Sh where Sh.CustomerID=Cu.CustomerID)


-- but there are ZERO rows returned... So, what is really being asked ? Are there any customers without Sales Orders ?

SELECT Cu.*
FROM Sales.Customer AS Cu
WHERE NOT EXISTS (select * from sales.SalesOrderHeader Sh where Sh.CustomerID=Cu.CustomerID)

-- and note that they are all "S" type customers - not individuals but Stores.
-- luckily, we only want ID and Name and the Sales.Stores table has both...

SELECT s.CustomerID, s.Name, SalesOrderNumber
FROM Sales.Store AS S
LEFT JOIN Sales.SalesOrderHeader AS Sh ON S.CustomerID = Sh.CustomerID

-- notice how any store without a salesorderheader has the SalesOrderNumber showing as NULL ? then that is what we are looking for :

SELECT s.CustomerID, s.Name
FROM Sales.Store AS S
LEFT JOIN Sales.SalesOrderHeader AS Sh ON S.CustomerID = Sh.CustomerID
WHERE SalesOrderNumber is NULL

-- however if you also want the contact firstname and lastname then there is still a lot more to do...

So, the question you have originally asked might not actually be as straight forward, so you do need to sometimes entertain our probing questions because the adventureworks database is an involved beast, and sometimes we dont know if it is trying to understand that database, or, the query, or maybe even be homework - because it is also used for homework which we often see, and simply because it is a rotten thing to use, and I think there are masochist teachers out there :)

Hope that goes part way to solving your problem... At least you can now see the two different styles of query used to test "not there" and also a few insights into adventureworks...

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 33562217
Hi John500,

I have just been through all your open "adventureworks" questions and they are definitely a little bit suspicious of "homework" in terms of what and how you ask the question.

So, I am not so surprised that it has been raised by a few experts that it does look like homework. Combined with your own inferences to a list of questions, crunch time, your reactions, it does invite various thoughts.

In terms of collaboration, then all you need to do is simply say if it is part of "homework" or not. Show us where the difficulties in understanding are, and we can help clarify those difficulties - or at least show us the attempts you have made thus far in trying to make your own queries work and we can help "debug" that with you.

Note the keyword here is "with you" rather than "for you". That is pretty much the key to our undertaking of "academic honesty".

We all need to follow the terms of use, and experts are only too happy to help where we can but it is dependant on you and how you interact / engage with the experts. It might seem we are picking on you unfairly, but that isnt meant to be the case. We do have guidelines and various rules which we need to take seriously otherwise we jeopardise our own participation within EE, and none of us want that.

In a seemingly strange twist, the more caring expert will always ask... So, when we ask things like "is this homework" we are not putting you on any witness stand as much as seeking clarification so we know how we can proceed to help you and stay within the rules at the same time. The uncaring expert might simply post an answer, but that doesnt really help. Think of it along the lines of "Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life."

You said "Nope" to the learning SQL question, thus indicating the need to understand the data relationships within Adventureworks, but that is not how your question was phrased, and really just makes it more of a mystery as to what is really being asked.

Moving forward, if you have more of these types of questions, then can I suggest you raise the difficulties you are actually having, where you got up to, and what help is needed (e.g. is it individuals or stores, is it sales or purchases) - I think you will find a different approach, and (admittedly) might still get the same old question "is this homework", but at least we will be able to help you resolve the problems so that you can then answer your list of questions directly and confidently.

Guess I now need to get off the pulpit as well, but it is important to clear up any misunderstandings... And now, curiosity really has my interests aroused... What are these questions for ? And of course you dont have to answer, but it is interesting, and Adventureworks does require a LOT of knowledge / play time :)

Cheers,
Mark Wills
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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

11 Experts available now in Live!

Get 1:1 Help Now