JOIN issue: Valid Syntax to refer to (In FROM clause) the Selected Fields from Query

[EagleOne]

2003

Setup: Need the results of three tables being 2-stepped joined.

Table1 is the main data table. Table2 and Table3 are validation tables for two separate fields in
Table1.

If Table1.Field3 is not in Table2 then Query1 results creates an Expression Field based upon the
JOIN Table1 to Table2 with IIF(IsNull(Table1.Field3),"Not Found","Found")

If Table1.Field5 (issue: can I use Query1.Field5 because Table1 is not available in Query2?) is not
in Table3 then Query2 results creates an Expression Field based upon the JOIN Table1 to Table3 with
IIF(IsNull(Table1.Field5),"Not Found","Found")

Plan: 1) Create/Save Query1 which joins Table1 to Table2
2) Create/Save Query2 which uses Query1 as 1st Joined table to 2nd Joined = Table3

ERROR in FROM: "JOIN Expression not supported" which is:

FROM Query1 LEFT JOIN Table3 ON Query1.Field3 = Table3.Field1

Note: Field3 from Query1 is actually SELECTED in Query2 as "Table1.Field5 AS BCN"
I believe that this is causing an error. How do I refer to fields setup in Query1 when called
effectively as Table1 in Query2's JOIN? i.e. as Table1.Field5 or as BCN?

Concerns:
1) How do I properly SQL-code Query1 in the 1st part of a LEFT JOIN?
2) How do I SELECT (SQL-code) in Query2, the fields created in Query1?
3) What is the logic or syntax error in my FROM statement?
4) Is there a better way?

TIA EagleOne

 

[Douglas J. Steele]

If Query2 has "Table1.Field5 AS BCN" as part of the Select, then the name of
the field is BCN, not Field5.

How about you show the complete SQL for all of the queries?

 

[Ken Sheridan]

You should be able to do it with a single query. If you want to indicate
independently which rows are 'found' in tables 2 or 3:

SELECT Table1.Field3, Table1.Field5,
IIF(Table2.Field3 IS NULL,"Not Found","Found") AS InTable2,
IIF(Table3.Field5 IS NULL,"Not Found","Found") AS InTable3
FROM (Table1 LEFT JOIN Table2
ON Table1.Field3 = Table2.Field3)
LEFT JOIN Table3
ON Table1.Field5 = Table3.Field5

If you want to indicate which rows are 'found' in both tables 2 and 3:

SELECT Field3, Field5,
IIF(EXISTS
(SELECT *
FROM Table2
WHERE Table2.Field3 = Table1.Field3)
AND EXISTS
(SELECT *
FROM Table3
WHERE Table3.Field5 = Table1.Field5),
"Found", "Not Found") AS IsFound
FROM Table1 ;

Ken Sheridan
Stafford, England

 

[EagleOne]

Doug,

I am working that as we speak.

BTW, I am new to SQL structure and syntax - guess that shows.

In Query2, how do I refer to the fields (SELECT) from Query1.

In Query1 do I have to code SQL-syntax Table1.Field1 AS BCN and refer to BCN in Query2?

WHERE statements in Query1 refer to Table1.Field(x). I guess that I do not have to include WHERE
clauses in Query2 that were in effect in Query1?????

EagleOne

 

[EagleOne]

Doug,

I may have it!

In Query2, I referred to at "AS" variables?? (What is the correct term?) created in Query1.

Also in Query2, I did not include the WHERE clause that I had in Query1 which I did in error I think
- as the data was already filtered in Query1??

So does Query2 run Query1 automatically? (I guess so? How much fun is that!)

 

[Douglas J. Steele]

Not sure I follow your questions.

In Query2, you'd refer to the fields in Query1 the same as you would if
Query1 was a table. In other words, if the fields in Query1 are BCN, Field3
and ABC, you'd use SELECT BCN, Field3, ABC FROM Query1.

If Query1 has SELECT Table1.Field1 AS BCN FROM Table1, then yes, you'd refer
to the field as BCN in Query2.

You do not have to duplicate where clauses in Query2 that are in Query1
unless Query2 introduces additional tables/queries not in Query1.

 

[EagleOne]

In SQL, how do I code to get the first five characters i.e. VBA's LEFT(Table1.Field1,1,5)?

 

[EagleOne]

Got it! Left(Table1.Field1,5)

Your comments/questions focused my searches. Thanks!

EagleOne

 

[Wolfgang Kais]

Hello "EagleOne".

2003

Setup: Need the results of three tables being 2-stepped joined.

Table1 is the main data table. Table2 and Table3 are validation
tables for two separate fields in Table1.

These seem to be Field3 (for Table1) and Field5 (for Table2).
As you didn't mention it, I guess that the corresponding fields in
Table2 and Table3 are also named Field3 and Field5.

If Table1.Field3 is not in Table2 then Query1 results creates an
Expression Field based upon the JOIN Table1 to Table2 with
IIF(IsNull(Table1.Field3),"Not Found","Found")

I don't see why this should mean "Not Found" in Table2, it looks
more like Table2.Field3 not found in Table1.Field3.
In my opinion, Query1 should look like this:
Select IIF(IsNull(Table2.Field3),"Not Found","Found") As Expr1
From Table1 Left Join Table2 On Table1.Field3 = Table2.Field3

If Table1.Field5 (issue: can I use Query1.Field5 because Table1
is not available in Query2?) is not in Table3 then Query2 results
creates an Expression Field based upon the JOIN Table1 to Table3
with IIF(IsNull(Table1.Field5),"Not Found","Found")

Concerning your "issue": since query2 joins Table1 and Table3,
Table1 IS available in Query2.
Similar to what I said above, Query2 I think that should look like:
Select IIF(IsNull(Table3.Field5),"Not Found","Found") As Expr1
From Table1 Left Join Table3 On Table1.Field5 = Table3.Field5

Plan:
1) Create/Save Query1 which joins Table1 to Table2
2) Create/Save Query2 which uses Query1 as 1st Joined table to
2nd Joined = Table3

I see. That is of course possible.

ERROR in FROM: "JOIN Expression not supported" which is:

FROM Query1 LEFT JOIN Table3 ON Query1.Field3 = Table3.Field1

Note: Field3 from Query1 is actually SELECTED in Query2 as
"Table1.Field5 AS BCN"

For this to work, Query1 has to return a field named Field3.
This field can be selected in Query2 as Query1.Field3 but neither
as Table1.Field5 nor BCN.

I believe that this is causing an error. How do I refer to fields
setup in Query1 when called effectively as Table1 in Query2's JOIN?
i.e. as Table1.Field5 or as BCN?

As I said: If Query1 returned (selected) a field named Field3, you
can't refer to it using any other name than Query1.Field3

Concerns:
1) How do I properly SQL-code Query1 in the 1st part of a LEFT JOIN?

.... FROM Table1 LEFT JOIN Table2 ON ...

2) How do I SELECT (SQL-code) in Query2, the fields created in
Query1?

SELECT Query1.FieldX FROM Query1 ...

3) What is the logic or syntax error in my FROM statement?

The most likely cause for the error is that Query1 does not return
a field named Field3. If "Field3" is to mean "the third field" and
that field was defined as Table1.Field5 As BCN in Query1, the name
of the field was BCN and therefore the join should read:
....FROM Query1 LEFT JOIN Table3 ON Query1.BCN = Table3.Field1

4) Is there a better way?

If only those values from Table2 and Table3 that compare with were
allowd in Table1, then: yes.
You could create relationships between the tables that enforce
referential integrity. Then all non-NULL values in the respective
fields on Table1 would mean "Found", and only NULL would mean
"Not Found". There would be no need to join the tables, you would
just have to check for NULL values.

 

[EagleOne]

Thank you for your time and knowledge!

EagleOne

Hello "EagleOne".



These seem to be Field3 (for Table1) and Field5 (for Table2).
As you didn't mention it, I guess that the corresponding fields in
Table2 and Table3 are also named Field3 and Field5.


I don't see why this should mean "Not Found" in Table2, it looks
more like Table2.Field3 not found in Table1.Field3.
In my opinion, Query1 should look like this:
Select IIF(IsNull(Table2.Field3),"Not Found","Found") As Expr1
From Table1 Left Join Table2 On Table1.Field3 = Table2.Field3


Concerning your "issue": since query2 joins Table1 and Table3,
Table1 IS available in Query2.
Similar to what I said above, Query2 I think that should look like:
Select IIF(IsNull(Table3.Field5),"Not Found","Found") As Expr1
From Table1 Left Join Table3 On Table1.Field5 = Table3.Field5


I see. That is of course possible.


For this to work, Query1 has to return a field named Field3.
This field can be selected in Query2 as Query1.Field3 but neither
as Table1.Field5 nor BCN.


As I said: If Query1 returned (selected) a field named Field3, you
can't refer to it using any other name than Query1.Field3


... FROM Table1 LEFT JOIN Table2 ON ...


SELECT Query1.FieldX FROM Query1 ...


The most likely cause for the error is that Query1 does not return
a field named Field3. If "Field3" is to mean "the third field" and
that field was defined as Table1.Field5 As BCN in Query1, the name
of the field was BCN and therefore the join should read:
...FROM Query1 LEFT JOIN Table3 ON Query1.BCN = Table3.Field1


If only those values from Table2 and Table3 that compare with were
allowd in Table1, then: yes.
You could create relationships between the tables that enforce
referential integrity. Then all non-NULL values in the respective
fields on Table1 would mean "Found", and only NULL would mean
"Not Found". There would be no need to join the tables, you would
just have to check for NULL values.

 

[EagleOne]

You could create relationships between the tables that enforce
referential integrity. Then all non-NULL values in the respective
fields on Table1 would mean "Found", and only NULL would mean
"Not Found". There would be no need to join the tables, you would
just have to check for NULL values.

Excellent! OK, I create a relationship among the three tables.

In a SQL query, what SQL code (or any other way) do I need to create fields in Query2 to reflect
that Table1.Field3 and Table1.Field were validated ("Found") or not validated ("Not Found") in the
respective tables Table2 and Table3?

Or, in other words, the SQL code in Query2 code to check for NULL values for Table2 & Table3?

TIA EagleOne

 

[EagleOne]

Now that I realize that I asked a stupid question, I'll explain:

Table1 is prepared off-site, and therefore, completely out of my control.
Therefore, I must validate the data myself. Which in turn, means that I
must run Query1 & Query2.

My question was stupid because if Table1 was being verified as data was being entered, (with
referential integrity to Table2 and Table3) I would not need to create additional fields with
Queries. Da!

EagleOne

 

[Wolfgang Kais]

Hello EagleOne.

I think that Ken's query will do the trick for you.

 

[EagleOne]

Very good. I almost did not see your reply.

Your answer gives me great insight to real solutions.

As a beginner, it is difficult to apply pure theory without the experience of actual solutions.

EagleOne

 

[EagleOne]

Ken,

Can I run both Selects from one query? The reason is I noticed that your 2nd-section code is:

SELECT Field3, Field5, ...... I would have expected "SELECT Table1.Field3, Table1.Field5,"
if run independently in a separate query.

EagleOne

 

[Sylvain Lafontaine]

You don't need to replace the Left Join with a subquery even in the case
where you would want to indicate which rows are 'found' in both tables 2 and
3 because you can test for both columns in a single IIF statement:

SELECT Table1.Field3, Table1.Field5,
IIF(Table2.Field3 IS Not NULL and Table3.field5 is Not Null,"Found","Not
Found") AS InTable2_And_InTable3,
FROM (Table1 LEFT JOIN Table2
ON Table1.Field3 = Table2.Field3)
LEFT JOIN Table3
ON Table1.Field5 = Table3.Field5

Using a Left Join is usually speedier than using a subquery, so I prefer to
use the second only when the conditions are too complex to be expressed by a
simple Left Join. However, both will give the same final result and are
equally easy to code for simple cases such as the one here.