asked on

SQL Subquery Help

Hi Guys and Gals

I have 2 SQL statements that i've created in MSAccess but ideally i want just one SQL.

Statement 1:

SELECT Roster.member, Attendance.date
FROM Roster, Attendance
GROUP BY Roster.member, Attendance.date;

Open in new window

This SQL creates a list of all members and all possible dates.

Statement 2:

SELECT JAGM1.member, JAGM1.Date
FROM (Attendance RIGHT JOIN JAGM1 ON (Attendance.member = JAGM1.member) AND (Attendance.date = JAGM1.date)) LEFT JOIN Roster ON JAGM1.member = Roster.member
WHERE (((JAGM1.Date)>=([Roster].[join_date])) AND ((Attendance.member) Is Null));

Open in new window

This query uses the result of JAGM1 (which is generated from statement 1) to find some unmatched records that fall on or after the join date.

Is it possible to combine the two SQL statements into one?

James

mbizup

Try this (you may have to rename your extsing query1 to something like JAGM1_Backup, sinc JAGM1 is used as an alias in this):

SELECT JAGM1.member, JAGM1.Date
FROM (Attendance RIGHT JOIN 
(SELECT Roster.member, Attendance.date
FROM Roster, Attendance
GROUP BY Roster.member, Attendance.date) AS JAGM1
ON (Attendance.member = JAGM1.member) AND (Attendance.date = JAGM1.date)) LEFT JOIN Roster ON JAGM1.member = Roster.member
WHERE (((JAGM1.Date)>=([Roster].[join_date])) AND ((Attendance.member) Is Null));

Open in new window

Delerium1978

ASKER

syntax error between line 5 & 6 - error in FROM clause.

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbizup

Cleaned up a little:

SELECT J1.member, J1.Date
FROM (Attendance RIGHT JOIN
(SELECT Roster.member, Attendance.date
FROM Roster, Attendance
GROUP BY Roster.member, Attendance.date) AS J1
ON Attendance.member = J1.member AND Attendance.date = J1.date) LEFT JOIN Roster ON J1.member = Roster.member
WHERE J1.Date >= Roster.join_date AND member Is Null

Delerium1978

ASKER

Perfect - many thanks!!!