Solved

SQL  Query question

Posted on 2013-01-13
7
287 Views
Last Modified: 2013-01-15
I need sql queries  for the questions below: I included the queries that I am using but gives me one saleperson only. I know that "two orders do not have items."    

Th tables are in the attached file.

If I need to use full outer join query then how can I implement that ?   Thank you

Which sales people have NOT sold anything?    Subquery version

SELECT s.Ename
FROM SALESPERSONS s
WHERE s.EmpID NOT IN(
SELECT o.EmpID
FROM ORDERS o
   , SALESPERSONS s
WHERE o.EmpID = s.EmpID);
GO

Which sales people have NOT sold anything? JOIN version (explicit/named JOIN)

SELECT s.Ename
FROM SALESPERSONS s
FULL OUTER  JOIN  ORDERS o ON o.EmpID = s.EmpID
WHERE o.EmpID IS NULL;
sqltables.doc
0
Comment
Question by:ocdc
  • 3
  • 3
7 Comments
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 500 total points
ID: 38772936
SELECT s.Ename 
FROM SALESPERSONS s
WHERE NOT EXISTS(
    SELECT 1
    FROM ORDER o WHERE o.EmpID = s.EmpID)

Open in new window

0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 38772937
Another:

SELECT s.Ename 
FROM SALESPERSONS s LEFT JOIN
    ORDER o WHERE o.EmpID = s.EmpID
WHERE o.EmpID IS NULL

Open in new window

0
 

Author Comment

by:ocdc
ID: 38773057
Which Customer  placed the most orders?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:ocdc
ID: 38773061
more info.  for    Which Customer  placed the most orders?  I need to show:

CUSTOMERS.custid, CUSTOMERS.cname, and a count
0
 

Author Comment

by:ocdc
ID: 38774177
I've requested that this question be deleted for the following reason:

no need
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38774178
I object to deleting this question.  I offered two different ways to resolve the original question before the Asker (1) completely changed the question and then (2) decided that s/he no longer wanted to proceed.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

910 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

23 Experts available now in Live!

Get 1:1 Help Now