Need to replace INNER with LEFT

  • Thread starter Thread starter Rick Dunmire
  • Start date Start date
R

Rick Dunmire

Hello everyone,

I have a little snag with a query join.
I need to replace an INNER JOIN with a LEFT JOIN but I get an error " Join
expression not supported"

This works "LEFT JOIN Inspection" below.

FROM (Customer INNER JOIN Vehicle ON Customer.pk_customer =
Vehicle.fk_customer) LEFT JOIN Inspection ON Vehicle.pk_vehicle =
Inspection.fk_vehicle

This doesn't "LEFT JOIN Inspection" below.

FROM QueryParms, (Customer INNER JOIN Vehicle ON Customer.pk_customer =
Vehicle.fk_customer) INNER JOIN (Company LEFT JOIN Inspection ON
Company.pk_company = Inspection.fk_company) ON Vehicle.pk_vehicle =
Inspection.fk_vehicle

Can this be fixed somehow?

Thanks
Rick Dunmire
 
Hi,


The easiest way would be to use embedded queries:

Q1: .... FROM Company LEFT JOIN Inspection ON Company.pk_company =
Inspection.fk_company


then, in your query:

FROM (Customer INNER JOIN Vehicle ON Customer.pk_customer =
Vehicle.fk_customer) INNER JOIN Q1 ON Vehicle.pk_vehicle = Q1.fk_vehicle



Note that the left join in Q1 is useless since the left join produces some
NULL in Inspection.fk_vehicle (else, use an INNER JOIN would be all what was
required), but the inner join you use, there after:

ON Vehicle.pk_vehicle = Inspection.fk_vehicle


removes all (doesn't keep any) of those same NULL. The Left join is thus
useless, as it should be replaced by an inner join, as far as the final
result is concerned.



Hoping it may help,
Vanderghast, Access MVP
 
Thanks Michel,

I need those null values also from the inspection table.
What I need to construct is if a field in the inspection table "Not Is Null"
then do something
and if the field "Is Null" then do something else.
Something like this.

If Inspection.sticker_exp_month = Is Null Then
Where Inspection.sticker_exp_month=1
ElseIf Inspection.sticker_exp_month = Not Is Null Then
Where Vehicle.license_plate_expiration_month=1

something like this if this makes any sense. With out the LEFT Join in the
Inspection table
I can't seem to achieve this result.
I have some sample info for this I think from another programmer I chatted
with (Peter) but
until I get the join figured out I am kinda stuck.
I see posts about If/Then statements and IIF stuff but I'm not sure how to
put it in my query, so
if the above looks childish well I am better at vb than access. hehehe
 
If Inspection.sticker_exp_month = Is Null Then
Where Inspection.sticker_exp_month=1
ElseIf Inspection.sticker_exp_month = Not Is Null Then
Where Vehicle.license_plate_expiration_month=1
Click to expand...

In a Query, you could use

WHERE Inspection.sticker_exp_month=NZ(sticker_exp_month, 1)

The syntax "<field> = (NOT) IS NULL"

is simply incorrect. To check the nullity of a field the correct
syntax would be

WHERE [fieldname] IS NULL

or

WHERE [fieldname] IS NOT NULL
 
Thanks John,

Unless I can get that inner join on inspection changed to a left join, I
will never get the null values I need.

"FROM QueryParms, (Customer INNER JOIN Vehicle ON Customer.pk_customer =
Vehicle.fk_customer) INNER JOIN (Company INNER JOIN Inspection ON
Company.pk_company = Inspection.fk_company) ON Vehicle.pk_vehicle =
Inspection.fk_vehicle"

I think I can (or hope I can) get the rest of the query done once I somehow
get that left join done.
If I remove two tables, I can do the left join but if I add either one of
the two tables back in I get the join error again.
"QueryParms and Company" QueryParms has no relationship and Company is
related to the Inspection table.

John Vinson said:
If Inspection.sticker_exp_month = Is Null Then
Where Inspection.sticker_exp_month=1
ElseIf Inspection.sticker_exp_month = Not Is Null Then
Where Vehicle.license_plate_expiration_month=1
Click to expand...

In a Query, you could use

WHERE Inspection.sticker_exp_month=NZ(sticker_exp_month, 1)

The syntax "<field> = (NOT) IS NULL"

is simply incorrect. To check the nullity of a field the correct
syntax would be

WHERE [fieldname] IS NULL

or

WHERE [fieldname] IS NOT NULL
Click to expand...
 
Hi,



In addition, remember that


NULL = NULL


returns NULL (unknown since it is unknown if two unknown quantities are
equal among themselves or not... they may, they maybe not, it is unknown).
Remember that in SQL a logical value is either True, either False, either
Unknown ( -1, 0, and NULL, respectively, in Jet).

So, that is why

WHERE fieldName = NULL

is NEVER true, so all records are eliminated.


That is why

FROM ( x LEFT JOIN unpreservedTable ON ... )
INNER JOIN y
ON unpreservedTable.Field = y.SomeFIeld


make the outer join useless.

The unpreserved table in the outer join, the table that would see
its data filled by NULL in case of no match for the outer join, will get
some null, ok, but then,

the INNER JOIN coming AFTER would compare those NULL and those NULL
will all be removed (NULL = NULL does not return true, NULL=anything does
not return True, so, any NULL comparison will fail and the record supplying
the NULL will be removed), making the inner most outer join useless,
replaceable but an Inner join, in pure logic.

(Note that JET would complain with such a FROM clause, anyhow, so,
with Jet, you have to use embedded query if you REALLY want to force that
.... strange execution).


You would probably have to change the outer most join to an outer
join... and since Jet will still find matter to complain (because the order
of execution is not necessary the one of the parenthesis, in jet), you will
have to use an embedded query sine the order of execution of the join among
themselves is then important (and we cannot use ( ) to describe it ) :

SELECT ...
FROM q1
LEFT JOIN y ON q1.somefield = y.someField



and q1, a saved query with

SELECT unreservedTable.somefield, ...
FORM x LEFT JOIN unpreservedTable ON ....


Having preserved your NULL through your joins, do not forget John'
suggestions when it is time to compare the fields that may have got those
NULL you work so hard to keep all along... Seems complex? well, just a
matter to be aware of NULL = NULL returns NULL, NULL = NULL is not
returning True... and every thing else follow.



Hoping it may help,
Vanderghast, Access MVP


John Vinson said:
If Inspection.sticker_exp_month = Is Null Then
Where Inspection.sticker_exp_month=1
ElseIf Inspection.sticker_exp_month = Not Is Null Then
Where Vehicle.license_plate_expiration_month=1
Click to expand...

In a Query, you could use

WHERE Inspection.sticker_exp_month=NZ(sticker_exp_month, 1)

The syntax "<field> = (NOT) IS NULL"

is simply incorrect. To check the nullity of a field the correct
syntax would be

WHERE [fieldname] IS NULL

or

WHERE [fieldname] IS NOT NULL
Click to expand...
 
Thanks everyone,

Wow, I need to swallow that down first, then rethink here.

Thanks again

Rick Dunmire


Michel Walsh said:
Hi,



In addition, remember that


NULL = NULL


returns NULL (unknown since it is unknown if two unknown quantities are
equal among themselves or not... they may, they maybe not, it is unknown).
Remember that in SQL a logical value is either True, either False, either
Unknown ( -1, 0, and NULL, respectively, in Jet).

So, that is why

WHERE fieldName = NULL

is NEVER true, so all records are eliminated.


That is why

FROM ( x LEFT JOIN unpreservedTable ON ... )
INNER JOIN y
ON unpreservedTable.Field = y.SomeFIeld


make the outer join useless.

The unpreserved table in the outer join, the table that would see
its data filled by NULL in case of no match for the outer join, will get
some null, ok, but then,

the INNER JOIN coming AFTER would compare those NULL and those NULL
will all be removed (NULL = NULL does not return true, NULL=anything does
not return True, so, any NULL comparison will fail and the record supplying
the NULL will be removed), making the inner most outer join useless,
replaceable but an Inner join, in pure logic.

(Note that JET would complain with such a FROM clause, anyhow, so,
with Jet, you have to use embedded query if you REALLY want to force that
... strange execution).


You would probably have to change the outer most join to an outer
join... and since Jet will still find matter to complain (because the order
of execution is not necessary the one of the parenthesis, in jet), you will
have to use an embedded query sine the order of execution of the join among
themselves is then important (and we cannot use ( ) to describe it ) :

SELECT ...
FROM q1
LEFT JOIN y ON q1.somefield = y.someField



and q1, a saved query with

SELECT unreservedTable.somefield, ...
FORM x LEFT JOIN unpreservedTable ON ....


Having preserved your NULL through your joins, do not forget John'
suggestions when it is time to compare the fields that may have got those
NULL you work so hard to keep all along... Seems complex? well, just a
matter to be aware of NULL = NULL returns NULL, NULL = NULL is not
returning True... and every thing else follow.



Hoping it may help,
Vanderghast, Access MVP


John Vinson said:
If Inspection.sticker_exp_month = Is Null Then
Where Inspection.sticker_exp_month=1
ElseIf Inspection.sticker_exp_month = Not Is Null Then
Where Vehicle.license_plate_expiration_month=1
Click to expand...

In a Query, you could use

WHERE Inspection.sticker_exp_month=NZ(sticker_exp_month, 1)

The syntax "<field> = (NOT) IS NULL"

is simply incorrect. To check the nullity of a field the correct
syntax would be

WHERE [fieldname] IS NULL

or

WHERE [fieldname] IS NOT NULL
Click to expand...
Click to expand...
 
If I remove two tables, I can do the left join but if I add either one of
the two tables back in I get the join error again.
Click to expand...

You'll almost certainly need to create and store a Left Join query of
two of the tables, and then create a query joining the third table to
this stored query by a Left Join. You'll get the Ambiguous Outer Joins
message otherwise.
 
Back
Top