[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL Subquery Help

Posted on 2012-03-29
5
Medium Priority
?
295 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

650 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