Solved

Nested SQL Statements

Posted on 2003-10-22
10
17,279 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

896 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

13 Experts available now in Live!

Get 1:1 Help Now