Solved

Nested SQL Statements

Posted on 2003-10-22
10
17,277 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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

743 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

11 Experts available now in Live!

Get 1:1 Help Now