Solved

Nested SQL Statements

Posted on 2003-10-22
10
17,284 Views
Last Modified: 2011-08-18
I want to get back a recordset based on a nested sql statement that returns a count of 0, but I can't remember the syntax. It needs to return the count based on a field in the parent sql statement.

Something like:

Select ID From Employees AS TABLE1 Where  (Select Count(X) From Customers Where TABLE1.ID = Customers.ID) = 0
0
Comment
Question by:tyy8
[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
  • 4
  • 3
  • 3
10 Comments
 
LVL 32

Expert Comment

by:bhess1
ID: 9600081
Does it have to be nested?  A LEFT JOIN would be simpler...

Select ID From Employees AS TABLE1
LEFT JOIN Customers ON TABLE1.ID = Customers.ID
WHERE Customers.ID Is Null

Or, you can use:

Select ID From Employees AS TABLE1
Where  Not Exists (Select c.ID From Customers c Where TABLE1.ID = C.ID)
0
 

Author Comment

by:tyy8
ID: 9600536
That was a simple representation on my part. Each SQL statement would be several tables.
0
 
LVL 32

Expert Comment

by:bhess1
ID: 9600555
I actually was making that assumption.  However, a left join may still be possible, otherwise use the Not Exists syntax, which will check for a non-zero count.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Expert Comment

by:Dexstar
ID: 9600667
tyy8,

You want to find all the employees that do not have a corresponding Customer record, right?
I would do it like this:

     SELECT ID FROM Employees WHERE ID NOT IN (SELECT ID FROM Customers)

It's called a sub-query.

Hope this helps,
Dex*


0
 

Author Comment

by:tyy8
ID: 9602441
Okay, without making this too long - I'll expand on what I specifically want to do (which is date related):


SELECT TBL1.ID, TBL2.Address, TBL3.Department From TBL1 INNER JOIN TBL2 ON TBL1.ID = TBL2.ID INNER JOIN TBL3 ON TBL2.EID = TBL3.EID WHERE (SELECT Count(PID) AS Count FROM Programs WHERE (8 BETWEEN MONTH(StartDate) AND MONTH(EndDate))) AND Programs.ID = TBL1.ID) = 0

In other words, the ID field from the outer SQL statement passes it's ID as a parameter to the inner SQL Statement and I'm interested in people who haven't had training in August because the count will be zero.
0
 
LVL 19

Accepted Solution

by:
Dexstar earned 50 total points
ID: 9602526
tyy8,

I don't think you'll be able to get SQL to work like that.  I would use a sub-query like I did before.  I'm a little confused on your tables, so I'll give you the general formula.

It's 2 steps.
1) Write a SQL statement that selects the IDs of Employees who DID get training in August.  Just select the EmployeeID field from the appropriate table where the dates are in August.  You don't need to count them, just get the IDs.  I think it might look something like this for your tables:

     SELECT ID FROM Programs WHERE 8 BETWEEN MONTH(StartDate) AND MONTH(EndDate)

2) Once you have that, write a select statement that selects all the data that you want, but uses the previous statement as a filter about which records to EXCLUDE.

     SELECT
          TBL1.ID, TBL2.Address, TBL3.Department
     FROM
          TBL1 INNER JOIN TBL2 ON
               TBL1.ID = TBL2.ID
          INNER JOIN TBL3 ON
               TBL2.EID = TBL3.EID
     WHERE
          TBL1.ID NOT IN (SELECT ID FROM Programs WHERE 8 BETWEEN MONTH(StartDate) AND MONTH(EndDate))

That will filter out all the IDs who are in the list of IDs who got training in August, therefore giving you a list of everyone who DID NOT get Training in August.

Hope that helps,
Dex*
0
 
LVL 32

Expert Comment

by:bhess1
ID: 9602572
We can do this with the Exists test:

SELECT TBL1.ID, TBL2.Address, TBL3.Department
FROM TBL1
INNER JOIN TBL2 ON
    TBL1.ID = TBL2.ID
INNER JOIN TBL3 ON
    TBL2.EID = TBL3.EID
WHERE
  Not Exists (Select PID From Programs WHERE Programs.ID = Tbl1.ID AND (8 Between Month(StartDate) And Month(EndDate))


Note that there is a possible logical flaw here.  Consider this data subset from Programs:

PID   StartDate   EndDate
 1     10/01/2000  09/01/2003

Note that 8 is not between Month(StartDate) and Month(EndDate), although August occurs twice in the date range.  If your data can never have this issue, then there is no problem.
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9602669
bhess,

How is your answer any different from mine?

Also, the might be a "logical" flaw, but I got that code from tyy8, so I am assuming that it is good enough for him.  It doesn't really pertain to his question too much anyway.

Dex*
0
 
LVL 32

Expert Comment

by:bhess1
ID: 9603089
Both your and my code should produce the same resultset.  My comments reflect my original comment, where I stated that an EXISTS test could be used for what tyy8 was looking for.  Which works better for tyy8 is the question, as these will usually generate different plans.

Also, I was pointing out the potential logic flaw to **tyy8**, not you.  tyy8 may not have fully considered this, as this is the type of error that I see crop up in code fairly frequently when dealing with dates -- forgetting that dates wrap around years.  

Another (and possibly better) example might have had a StartDate of 12/31/2002 and and EndDate of 12/30/2003.  8 is not Between 12 and 12, but this would clearly seem to be data that the query should be retrieving.
0
 

Author Comment

by:tyy8
ID: 9603959
That's Great! Thanks.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Agent Job Error 13 83
SQL Job Failed 6 31
SQL Server replace between tags 6 32
Enable TLS 1.2 for SQL 2012 Web Edition 1 25
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

710 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