Nested SQL Statements

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
tyy8Asked:
Who is Participating?
 
DexstarCommented:
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
 
Brendt HessSenior DBACommented:
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
 
tyy8Author Commented:
That was a simple representation on my part. Each SQL statement would be several tables.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Brendt HessSenior DBACommented:
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
 
DexstarCommented:
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
 
tyy8Author Commented:
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
 
Brendt HessSenior DBACommented:
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
 
DexstarCommented:
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
 
Brendt HessSenior DBACommented:
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
 
tyy8Author Commented:
That's Great! Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.