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

N

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
 
J

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
 
A

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.
Click to expand...

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
 
B

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

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

N said:
Greetings,
Click to expand...
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.
Click to expand...
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.
Click to expand...
It looks like some other SQLs include a WITH statement, allowing
referrals to subqueries. Does Access have anything like that?
Click to expand...
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;
Click to expand...
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.
Click to expand...
I know for this example I can do a WHERE NOT IN, but I'd like to know
how to do this specifically.
Click to expand...
Thanks,
N Lee
Click to expand...
Click to expand...

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

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;
Click to expand...
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.
Click to expand...

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
Click to expand...

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

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,
Click to expand...
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.
Click to expand...
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.
Click to expand...
It looks like some other SQLs include a WITH statement, allowing
referrals to subqueries. Does Access have anything like that?
Click to expand...
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;
Click to expand...
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.
Click to expand...
I know for this example I can do a WHERE NOT IN, but I'd like to know
how to do this specifically.
Click to expand...
Thanks,
N Lee
Click to expand...
Click to expand...

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.
 
J

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

John said:
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

N said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subqueries 4
Q re Subqueries (Join Predicate) in Access 10
Question about Queries with Subqueries 7
not in criteria fails 2
Heavy Duty Union Subqueries 4
How to LEFT JOIN with 2 conditions fails in Access and .NET Query Builder? 3
Sum of multiple fields into single field from subqueries 5
SubQueries 10

Top