?
Solved

SQL Subquery Help

Posted on 2012-03-29
5
Medium Priority
?
294 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 37782370
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
ID: 37782392
syntax error between line 5 & 6 - error in FROM clause.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 37782415
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
ID: 37782479
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
ID: 37782521
Perfect - many thanks!!!
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, we’ll look at how to deploy ProxySQL.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

741 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