Solved

SQL  Query question

Posted on 2013-01-13
7
288 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

773 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