Solved

SQL Subquery Help

Posted on 2012-03-29
5
280 Views
Last Modified: 2012-03-29
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
0
Comment
Question by:Delerium1978
  • 3
  • 2
5 Comments
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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

0
 

Author Comment

by:Delerium1978
Comment Utility
syntax error between line 5 & 6 - error in FROM clause.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
Comment Utility
Try this:
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 ((Attendance.member) Is Null));

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
0
 

Author Closing Comment

by:Delerium1978
Comment Utility
Perfect - many thanks!!!
0

Featured Post

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!

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

763 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

10 Experts available now in Live!

Get 1:1 Help Now