How can I LEFT JOIN two subqueries in the same SELECT statement?

[N L]

Greetings,

I have one table, and a few queries that I want to run off of that
table. I would also like to view the results of LEFT JOIN operations
on those queries, and do it in a single SELECT statement. It isn't
practical for me to create and maintain a bunch of stored queries that
I can join.

I have figured out that I can put subqueries within parentheses in my
FROM clause, but then I can't figure out how to refer to the field on
which to join in the ON clause.

It looks like some other SQLs include a WITH statement, allowing
referrals to subqueries. Does Access have anything like that?

Here's what I have:
SELECT Employee_Table.*
FROM
(SELECT Employee_Table.*
FROM Employee_Table
WHERE (Employee_Table.Date)="January")
LEFT JOIN
(SELECT Employee_Table.*
FROM Employee_Table
WHERE (Employee_Table.Date)="February")
ON ??Subquery A??.[EMPLOYEE ID]=??Subquery B??.[EMPLOYEE ID]
WHERE ??Subquery A??.[EMPLOYEE ID]) Is Null;

The idea is that I want to find employees who joined in February. I
don't, however, know what to replace "??Subquery A??" and "??Subquery
B??" with.

I know for this example I can do a WHERE NOT IN, but I'd like to know
how to do this specifically.

Thanks,
N Lee

 

[John Spencer]

Assign names to the subqueries if you want to reference them elsewhere in the
query. So you would end up with something that looks like

SELECT JanT.*
FROM
(SELECT Employee_Table.*
FROM Employee_Table
WHERE (Employee_Table.Date)="January") as JanT
LEFT JOIN
(SELECT Employee_Table.*
FROM Employee_Table
WHERE (Employee_Table.Date)="February") as FebT
ON JanT.[EMPLOYEE ID]=FebT.[EMPLOYEE ID]
WHERE FebT.[EMPLOYEE ID]) Is Null;

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Amy Blankenship]

Here's what I have:
SELECT Employee_Table.*
FROM
(SELECT Employee_Table.*
FROM Employee_Table
WHERE (Employee_Table.Date)="January")
LEFT JOIN
(SELECT Employee_Table.*
FROM Employee_Table
WHERE (Employee_Table.Date)="February")
ON ??Subquery A??.[EMPLOYEE ID]=??Subquery B??.[EMPLOYEE ID]
WHERE ??Subquery A??.[EMPLOYEE ID]) Is Null;

The idea is that I want to find employees who joined in February. I
don't, however, know what to replace "??Subquery A??" and "??Subquery
B??" with.

I don't think that this is the right approach. For one thing, the Employee
ID will always be null, because of your WHERE clause. Your query is very
confused, so without more information about what is in your table and why
you need to look at employee dates (don't use Date as a field name BTW) in
January to determine what employees joined in February. So I can only offer
you some pointers.

You can use the AS keyword to rename a subquery

(SELECT Employee_Table.* FROM Employee_Table WHERE
Employee_Table.dateField="February") AS SubQueryB

You can use a UNION Query to "stack" the results of different queries

(SELECT Employee_Table.* FROM Employee_Table WHERE
Employee_Table.dateField="January")

UNION ALL

(SELECT Employee_Table.* FROM Employee_Table WHERE
Employee_Table.dateField="February")

Note that in Access when you apply a WHERE clause to the outside of a Left
Join, it will "frustrate" the join, so it won't be a true left join...it
goes back to being an INNER JOIN. For more on that
http://tinyurl.com/38xg4o.

Hope something in there helps you.

-Amy

 

[Brian]

Access does not have a WITH.
I have however noted that using the stored queries is a better choice since
it is not any slower and easier to change and read.

If you want to find the ones in February why don't you just do
SELECT Employee_Table.*
FROM Employee_Table
WHERE Employee_Table.Date="February"

I don't quite follow what you are doing with this query, it is missing a
Join somewhere, however, to answer you question of what to replace
"??Subquery A??" and "??Subquery > B??" with you would do something like
SELECT Employee_Table.*
FROM (Employee_Table LEFT JOIN
(SELECT Employee_Table.* FROM Employee_Table WHERE Date="January") AS b ON
Employee_Table.[EMPLOYEE ID] = b.[EMPLOYEE ID] )
LEFT JOIN (SELECT Employee_Table.* FROM Employee_Table WHERE
Date="February") AS a ON Employee_Table.[EMPLOYEE ID] = a.[EMPLOYEE ID]
WHERE a.[EMPLOYEE ID] Is Null;


Brian

 

[N L]

Assign names to the subqueries if you want to reference them elsewhere in the
query. So you would end up with something that looks like

SELECT JanT.*
FROM
(SELECT Employee_Table.*
FROM Employee_Table
WHERE (Employee_Table.Date)="January") as JanT
LEFT JOIN
(SELECT Employee_Table.*
FROM Employee_Table
WHERE (Employee_Table.Date)="February") as FebT
ON JanT.[EMPLOYEE ID]=FebT.[EMPLOYEE ID]
WHERE FebT.[EMPLOYEE ID]) Is Null;

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Greetings,

I have one table, and a few queries that I want to run off of that
table. I would also like to view the results of LEFT JOIN operations
on those queries, and do it in a single SELECT statement. It isn't
practical for me to create and maintain a bunch of stored queries that
I can join.

I have figured out that I can put subqueries within parentheses in my
FROM clause, but then I can't figure out how to refer to the field on
which to join in the ON clause.

It looks like some other SQLs include a WITH statement, allowing
referrals to subqueries. Does Access have anything like that?

Here's what I have:
SELECT Employee_Table.*
FROM
(SELECT Employee_Table.*
FROM Employee_Table
WHERE (Employee_Table.Date)="January")
LEFT JOIN
(SELECT Employee_Table.*
FROM Employee_Table
WHERE (Employee_Table.Date)="February")
ON ??Subquery A??.[EMPLOYEE ID]=??Subquery B??.[EMPLOYEE ID]
WHERE ??Subquery A??.[EMPLOYEE ID]) Is Null;

The idea is that I want to find employees who joined in February. I
don't, however, know what to replace "??Subquery A??" and "??Subquery
B??" with.

I know for this example I can do a WHERE NOT IN, but I'd like to know
how to do this specifically.

Thanks,
N Lee

Wonderful. I thought it would be something simple like that. Thank you
so much.

 

[N L]

Here's what I have:
SELECT Employee_Table.*
FROM
(SELECT Employee_Table.*
FROM Employee_Table
WHERE (Employee_Table.Date)="January")
LEFT JOIN
(SELECT Employee_Table.*
FROM Employee_Table
WHERE (Employee_Table.Date)="February")
ON ??Subquery A??.[EMPLOYEE ID]=??Subquery B??.[EMPLOYEE ID]
WHERE ??Subquery A??.[EMPLOYEE ID]) Is Null;

The idea is that I want to find employees who joined in February. I
don't, however, know what to replace "??Subquery A??" and "??Subquery
B??" with.

I don't think that this is the right approach. For one thing, the Employee
ID will always be null, because of your WHERE clause. Your query is very
confused, so without more information about what is in your table and why
you need to look at employee dates (don't use Date as a field name BTW) in
January to determine what employees joined in February. So I can only offer
you some pointers.

You can use the AS keyword to rename a subquery

(SELECT Employee_Table.* FROM Employee_Table WHERE
Employee_Table.dateField="February") AS SubQueryB

You can use a UNION Query to "stack" the results of different queries

(SELECT Employee_Table.* FROM Employee_Table WHERE
Employee_Table.dateField="January")

UNION ALL

(SELECT Employee_Table.* FROM Employee_Table WHERE
Employee_Table.dateField="February")

Note that in Access when you apply a WHERE clause to the outside of a Left
Join, it will "frustrate" the join, so it won't be a true left join...it
goes back to being an INNER JOIN. For more on thathttp://tinyurl.com/38xg4o.

Hope something in there helps you.

-Amy

The 'AS' was what I was looking for. You're right that I'd be getting
all nulls. I switched the right and left sides of my join.
I'll talk to the table designer about his field names.
Thanks for your help, and the link.

 

[N L]

Access does not have a WITH.
I have however noted that using the stored queries is a better choice since
it is not any slower and easier to change and read.

If you want to find the ones in February why don't you just do
SELECT Employee_Table.*
FROM Employee_Table
WHERE Employee_Table.Date="February"

I don't quite follow what you are doing with this query, it is missing a
Join somewhere, however, to answer you question of what to replace
"??Subquery A??" and "??Subquery > B??" with you would do something like
SELECT Employee_Table.*
FROM (Employee_Table LEFT JOIN
(SELECT Employee_Table.* FROM Employee_Table WHERE Date="January") AS b ON
Employee_Table.[EMPLOYEE ID] = b.[EMPLOYEE ID] )
LEFT JOIN (SELECT Employee_Table.* FROM Employee_Table WHERE
Date="February") AS a ON Employee_Table.[EMPLOYEE ID] = a.[EMPLOYEE ID]
WHERE a.[EMPLOYEE ID] Is Null;

Brian

Greetings,

I have one table, and a few queries that I want to run off of that
table. I would also like to view the results of LEFT JOIN operations
on those queries, and do it in a single SELECT statement. It isn't
practical for me to create and maintain a bunch of stored queries that
I can join.

I have figured out that I can put subqueries within parentheses in my
FROM clause, but then I can't figure out how to refer to the field on
which to join in the ON clause.

It looks like some other SQLs include a WITH statement, allowing
referrals to subqueries. Does Access have anything like that?

Here's what I have:
SELECT Employee_Table.*
FROM
(SELECT Employee_Table.*
FROM Employee_Table
WHERE (Employee_Table.Date)="January")
LEFT JOIN
(SELECT Employee_Table.*
FROM Employee_Table
WHERE (Employee_Table.Date)="February")
ON ??Subquery A??.[EMPLOYEE ID]=??Subquery B??.[EMPLOYEE ID]
WHERE ??Subquery A??.[EMPLOYEE ID]) Is Null;

The idea is that I want to find employees who joined in February. I
don't, however, know what to replace "??Subquery A??" and "??Subquery
B??" with.

I know for this example I can do a WHERE NOT IN, but I'd like to know
how to do this specifically.

Thanks,
N Lee

I was looking to match nulls on a left join. Sorry if I didn't explain
that. The workings of my query were not really central to my question
- it was really about how to refer to subqueries. I agree about the
stored queries. I would do that, but I want to be able to run them
programmaticlaly, and there would be a great number of queries I'd
have to create in Access, which isn't worth my time.

 

[John Spencer]

I just noted that you said you wanted employees who joined in February. In
that case you need to make a few small changes to the query.

SELECT FebT.*
FROM
(SELECT Employee_Table.*
FROM Employee_Table
WHERE (Employee_Table.Date)="January") as JanT
RIGHT JOIN
(SELECT Employee_Table.*
FROM Employee_Table
WHERE (Employee_Table.Date)="February") as FebT
ON JanT.[EMPLOYEE ID]=FebT.[EMPLOYEE ID]
WHERE JanT.[EMPLOYEE ID]) Is Null;

Although what you say and your query leads me to think you could have a
mal-designed table structure.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Assign names to the subqueries if you want to reference them elsewhere
in the query. So you would end up with something that looks like

SELECT JanT.*
FROM
(SELECT Employee_Table.*
FROM Employee_Table
WHERE (Employee_Table.Date)="January") as JanT
LEFT JOIN
(SELECT Employee_Table.*
FROM Employee_Table
WHERE (Employee_Table.Date)="February") as FebT
ON JanT.[EMPLOYEE ID]=FebT.[EMPLOYEE ID]
WHERE FebT.[EMPLOYEE ID]) Is Null;

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Greetings,

I have one table, and a few queries that I want to run off of that
table. I would also like to view the results of LEFT JOIN operations
on those queries, and do it in a single SELECT statement. It isn't
practical for me to create and maintain a bunch of stored queries that
I can join.

I have figured out that I can put subqueries within parentheses in my
FROM clause, but then I can't figure out how to refer to the field on
which to join in the ON clause.

It looks like some other SQLs include a WITH statement, allowing
referrals to subqueries. Does Access have anything like that?

Here's what I have:
SELECT Employee_Table.*
FROM
(SELECT Employee_Table.*
FROM Employee_Table
WHERE (Employee_Table.Date)="January")
LEFT JOIN
(SELECT Employee_Table.*
FROM Employee_Table
WHERE (Employee_Table.Date)="February")
ON ??Subquery A??.[EMPLOYEE ID]=??Subquery B??.[EMPLOYEE ID]
WHERE ??Subquery A??.[EMPLOYEE ID]) Is Null;

The idea is that I want to find employees who joined in February. I
don't, however, know what to replace "??Subquery A??" and "??Subquery
B??" with.

I know for this example I can do a WHERE NOT IN, but I'd like to know
how to do this specifically.

Thanks,
N Lee