Solved

SQL  Query question

Posted on 2013-01-13
7
286 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
Comment Utility
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
Comment Utility
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
Comment Utility
Which Customer  placed the most orders?
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:ocdc
Comment Utility
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
Comment Utility
I've requested that this question be deleted for the following reason:

no need
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

772 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

12 Experts available now in Live!

Get 1:1 Help Now