By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,985 Members | 1,885 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,985 IT Pros & Developers. It's quick & easy.

nested sql

P: 2
Hi
I have a query that left joins 2 queries. The query works in Access but i want to use the source code in ASP and need to re-write it as a nested query but im not sure how to do that.

MyStaff selects all staff who fall under a specific supervisor including that supervisor:
SELECT Staff.ID, Staff.Name
FROM Staff
WHERE (((Staff.ID)="***")) OR (((Staff.SupervisorID)="***"));

LeaveTaken selects all staff under a supervisor who have taken leave and sums their leave totals:
SELECT DISTINCT Entries.StaffID, Sum(Entries.NumDays) AS Leave
FROM Entries
GROUP BY Entries.StaffID, Entries.Type, Entries.UploadedBy
HAVING (((Entries.Type)="L") AND ((Entries.UploadedBy)="***"));

Query3 left joins MyStaff and LeaveTaken to view all staff of a supervisor and if they have taken leave or not:
SELECT MyStaff.Name, LeaveTaken.Leave
FROM MyStaff LEFT JOIN LeaveTaken ON MyStaff.ID = LeaveTaken.StaffID;

My problem is i will need to substitute the value for Staff.ID, Staff.SupervisorID and Entries.UploadedBy with the Cookie of the supervisor who logged-in and i cannot see that code in Query3. Im not sure how to rewrite Query3 as a nessed query.

Thanks Grant
Oct 26 '06 #1
Share this Question
Share on Google+
1 Reply


MMcCarthy
Expert Mod 10K+
P: 14,534
try something like this:

SELECT Staff.Name, Sum(Entries.NumDays) AS Leave
FROM Staff LEFT JOIN Entries
ON Staff.StaffID=Entries.StaffID
WHERE (((Staff.ID)="***")) OR (((Staff.SupervisorID)="***"))
GROUP BY Staff.Name, Entries.Type, Entries.UploadedBy
HAVING (((Entries.Type)="L") AND ((Entries.UploadedBy)="***"));
Oct 27 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.