Correleted Sub Query with joins and aliases

[Guest]

SELECT B.NAME, B.ID, TEST_DATA_1_2.ID1, TEST_2.NAME
FROM TEST_1 AS B, TEST_2, TEST_DATA_1_2
WHERE B.ID =(SELECT A.ID1 FROM TEST_DATA_1_2 A INNER JOIN B ON B.ID = A.ID1)
AND TEST_2.ID = (SELECT A.ID2 FROM TEST_DATA_1_2 A WHERE TEST_2.ID = A.ID2);


I want all the rows from table test_1 and only matching rows from table test_2
When I run this query, I get the message
The Microsoft Jet database engine cannot find the input table or query 'B'
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

 

[Jason Lepack]

Let's get this straight-ish...

My guesses:
TEST_1
ID_1 - number - pk
NAME_1 - text (Name is a reserved word)

TEST_2
ID_2 - number - pk
NAME_2 - text

Test_1_2 (I assume that this is a many-to-many join table)
ID_1 - number - fk to Test_1.ID_1
ID_2 - number - fk to Test_2.ID_2

This is the SQL that Access gives me when I use the designer, I think
this should satisfy your situation, let me know:

SELECT TEST_1.ID_1,
TEST_1.NAME_1,
TEST_2.ID_2,
TEST_2.NAME_2
FROM TEST_2
RIGHT JOIN (TEST_1
LEFT JOIN TEST_1_2
ON TEST_1.ID_1 = TEST_1_2.ID_1)
ON TEST_2.ID_2 = TEST_1_2.ID_2;

 

[Guest]

I modified your stuff to look like this and I think it does do what I want.
SELECT TEST_1.ID,
TEST_1.NAME,
TEST_2.ID,
TEST_2.NAME
FROM TEST_2
RIGHT JOIN (TEST_1 LEFT JOIN TEST_DATA_1_2 ON TEST_1.ID =
TEST_DATA_1_2.ID1) ON TEST_2.ID = TEST_DATA_1_2.ID2 ORDER BY TEST_1.ID;

I also tried
SELECT A.ID, A.NAME, C.ID1, C.ID2, B.ID, B.NAME
FROM (TEST_1 AS A LEFT JOIN TEST_DATA_1_2 AS C ON A.ID=C.ID1) LEFT JOIN
TEST_2 AS B ON C.ID2=B.ID
ORDER BY A.ID;

and it gave the same results as yours did. I would like to do it with
subqueries though.


Thank you Jason,


Kirk

 

[Guest]

You see, I don't want to have to do mutiple joins with 6 or 7 tables using
this syntax

 

[Jason Lepack]

If you're dealing with a lot of data then a correlated subquery will
execute much slower.

In your initial example there are two queries executed for each row
returned by the main query.

You could use the old cartesian join and filter, but I wouldn't
suggest it as it is usually slower as well, but I think it will
perform better than your subqueries. I also don't know how to create
a left join using this method.

I would really suggest using the MS Access Join Syntax, because Access
understands it better. When I use T-SQL on SQL Server I write all of
my querys using SQL, however when it comes to Access and all those
brackets, I just use the query designer, then I don't have to worry
about fussing through the ugly looking SQL that it spits out.

Cheers,
Jason Lepack

 

[Jason Lepack]

You could try this too, the second query in the union is to handle the
left-ness of the join and pick up the records that didn't have
matches. Either way, I still stick with my recommendation the
previous post.

SELECT
TEST_1.ID_1,
TEST_1.NAME_1,
TEST_2.ID_2,
TEST_2.NAME_2
FROM
TEST_1, TEST_1_2, TEST_2
WHERE
TEST_1.ID_1=TEST_1_2.ID_1
AND TEST_2.ID_2=TEST_1_2.ID_2
UNION ALL
SELECT
TEST_1.ID_1,
TEST_1.NAME_1,
NULL,
NULL
FROM
TEST_1
WHERE NOT EXISTS (
SELECT 1
FROM TEST_1_2
WHERE
TEST_1_2.ID_1 = TEST_1.ID_1)

 

[Guest]

I tried your query and it returned only one row. I also tried the query tool
from access and it is probably the way to go. Thank you for your ideas.
They're good.

Kirk

SELECT
TEST_1.ID,
TEST_1.NAME,
TEST_2.ID,
TEST_2.NAME
FROM
TEST_1, TEST_DATA_1_2, TEST_2
WHERE
TEST_1.ID=TEST_DATA_1_2.ID1
AND TEST_2.ID=TEST_DATA_1_2.ID1
UNION ALL
SELECT
TEST_1.ID,
TEST_1.NAME,
NULL,
NULL
FROM
TEST_1
WHERE NOT EXISTS (
SELECT 1
FROM TEST_DATA_1_2
WHERE
TEST_DATA_1_2.ID1 = TEST_1.ID)

 

[Guest]

Jason,

Here's a query that I need to work designed by the tool in access.


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

I can't see the version of the query where major needs to have a left join
on for the COMMITTEE_MAJOR table. I need to print all the rows shown in the
COMMITTEE_MAJOR tabole.



when I attempt to run it it 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

I'm not familiar with the design tool. how would I build the subquery it is
suggesting?

Kirk