Solved

Nested SQL Statements

Posted on 2003-10-22
10
17,280 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
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

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

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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

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