Correlated Sub Query with joins in the where clause

[kmccoskey]

I want to do the subquery as follows but the left join will not work in
access. The reason is that I need to have
the ability to use subqueries with joins in the where clause so I can easily
do multiple joins. Any ideas?

EQUA JOIN

SELECT A.ID, A.NAME, C.ID1, C.ID2
FROM TEST_1 A, TEST_DATA_1_2 C
WHERE A.ID = (SELECT X.ID FROM TEST_1 X WHERE X.ID=C.ID1)

LEFT JOIN don't work
SELECT A.ID, A.NAME, C.ID1, C.ID2
FROM TEST_1 A, TEST_DATA_1_2 C
WHERE A.ID = (SELECT X.ID FROM TEST_1 X INNER JOIN C ON X.ID=C.ID1)

Error Message: The Microsoft Jet database engine cannot find the input
table or query 'C' Make sure it exists and that its name is spelled
correctly. If I don't use a join the query works but it doesn't allow me to
get all the rows from table test_1

 

[Jamie Collins]

I want to do the subquery as follows but the left join will not work in
access. The reason is that I need to have
the ability to use subqueries with joins in the where clause so I can easily
do multiple joins. Any ideas?

EQUA JOIN

SELECT A.ID, A.NAME, C.ID1, C.ID2
FROM TEST_1 A, TEST_DATA_1_2 C
WHERE A.ID = (SELECT X.ID FROM TEST_1 X WHERE X.ID=C.ID1)

LEFT JOIN don't work
SELECT A.ID, A.NAME, C.ID1, C.ID2
FROM TEST_1 A, TEST_DATA_1_2 C
WHERE A.ID = (SELECT X.ID FROM TEST_1 X INNER JOIN C ON X.ID=C.ID1)

Error Message: The Microsoft Jet database engine cannot find the input
table or query 'C' Make sure it exists and that its name is spelled
correctly. If I don't use a join the query works but it doesn't allow me to
get all the rows from table test_1

I think the following is equivalent to your 'equi-join':

SELECT A.ID, A.NAME, C.ID1, C.ID2
FROM TEST_1 A INNER JOIN TEST_DATA_1_2 C
ON A.ID = C.ID1;

In which case, the INNER JOIN can be changed to LEFT OUTER JOIN and
works for me.

FWIW you can do multiple joins in the FROM clause of the main query
without using subqueries in the WHERE clause.

Jamie.

--

 

[kmccoskey]

But the syntax is tough unless you just use the query tool. I'd like to do
this without using the query tool from access

 

[Jamie Collins]

But the syntax is tough unless you just use the query tool. I'd like to do
this without using the query tool from access

Eye of the beholder and all that but I think I'm right in saying that
most folk find it easier to putting all the tables and joins in the
FROM clause; I for one manage it without the query builder tool thing!
As you point out, your approach doesn't work with outer joins, plus
you're version needlessly uses on of the table a second time.

Jamie.

--

 

[kmccoskey]

I want to get all the records shown in the COMMITTEE_MAJOR table and only
matching rows in the MAJORS table. Access is having trouble with this.

When I attempt to edit the join for the fields COMMITTEE_MAJOR.ACAD_PLAN and
MAJORS.ACAD_PLAN access gives me the following message.

the sql statement could not be executed because it contains ambiguous outer
joins. to force one of the joins to be performed first, create a separate
query that performs the first join and then include that query in your sql
statement

SELECT COMMITTEE_SCHOLARSHIP.COMMITTEE_ID, CRITERIA_ANALYSIS.EMPLID,
MAJORS.ACAD_PLAN
FROM ((MAJORS INNER JOIN CRITERIA_ANALYSIS ON MAJORS.EMPLID =
CRITERIA_ANALYSIS.EMPLID) INNER JOIN COMMITTEE_MAJOR ON MAJORS.ACAD_PLAN =
COMMITTEE_MAJOR.ACAD_PLAN) INNER JOIN COMMITTEE_SCHOLARSHIP ON
COMMITTEE_MAJOR.COMMITTEE_ID = COMMITTEE_SCHOLARSHIP.COMMITTEE_ID;