adding criteria to LEFT JOIN - SQL

[Guest]

I am having difficulty adding criteria to a nested series of left joins
I want to do this in SQL as the specifics are altered on the fly.

I want to left join "AFTER" the criteria reduce each of the tables, but I
can only get the WHERE(s) to work after the joins have combined the tables
thus defeating the left join.

My code is rather complex so to limit this to one specific question, I am
posting a section of Allen Browne's pedigree code (nested Left joins - Self
Join) and asking how would you add criteria to this so that only the names of
horses WHERE [currenttable].Color = 'brown" would show up and other cells
would be null?

I have been trying to add these lines
WHERE DAM.Color = 'brown"
WHERE Sire.Color = "Brown"

I think that it's just a syntax isue but don't they need to be before the ON
statements to allow horses without "brown" parents to still print out but
with null parent names?

SELECT DISTINCTROW TblHorses.Name, Sire.Name, Dam.Name _
FROM (TblHorses_
LEFT JOIN TblHorses AS Sire _ '<======Tried here
ON TblHorses.SireID = Sire.ID) _
LEFT JOIN TblHorses AS Dam _ '<======Tried here
ON TblHorses.DamID = Dam.ID _'<======get incorrect results from here
ORDER BY TblHorses.Name;

THANKS ALL

 

[Tim Ferguson]

=?Utf-8?B?VmFjYXRpb24ncyBPdmVy?=

I am having difficulty adding criteria to a nested series of left joins
I want to do this in SQL as the specifics are altered on the fly.

SELECT DISTINCTROW TblHorses.Name, Sire.Name, Dam.Name
FROM (
tblHorses LEFT JOIN tblHorses AS Sire
ON TblHorses.SireID = Sire.ID
)

LEFT JOIN TblHorses AS Dam
ON TblHorses.DamID = Dam.ID

WHERE dam.color = "brown"
AND sire.color = "brown"

ORDER BY TblHorses.Name;

Another version uses sub-select statements in the FROM clause.

SELECT DISTINCTROW TblHorses.[Name] AS Horse,
Sire.[Name] AS HorsesSire,
Dam.[Name] AS HorsesDam

FROM (
tblHorses LEFT JOIN (
SELECT sire.ID, sire.[Name] FROM tblHorses AS Sire
WHERE sire.color = "brown"
)
ON TblHorses.SireID = Sire.ID
)

LEFT JOIN (
SELECT dam.ID, dam.[Name] FROM TblHorses AS Dam
WHERE dam.color = "brown"
)
ON TblHorses.DamID = Dam.ID

ORDER BY TblHorses.Name;

(I am not completely sure of the legality of the second one; you may need
to move where the aliases are defined outside the brackets). I strongly
suspect that the db engine will use the same query path for both of
these, but it would be interesting to benchmark them formally.

By the way, there is very little point in posting illegal queries and
asking for help. You won't win many friends when they have to debug
errors that you introduced by retyping into your newsreader. It's
preferable by far, as well as quicker for you, to copy-and-paste the
original code.

All the best


Tim F

 

[Guest]

ON is WHERE !!!

Thanks - with your direction( and hours of trial and error) I discovered the
following:

In a Left Join- the On statement is also the WHERE statement. To filter the
internal nested join BEFORE outer nesting the syntax is:

ON (sire.color = "Brown" AND Sire.ID = tblhorses.ID)

)

LEFT JOIN TblHorses AS Dam

ON (Dam.color = "Brown" AND Dam.ID = tblhorses.ID)

PS: as to the AS...I only had issues with aliasing the first table, the
other tables in the nesting seem to accept their alises.

FROM (
tblHorses LEFT JOIN tblHorses AS Sire

worked just fine...so far

THANKS again

=?Utf-8?B?VmFjYXRpb24ncyBPdmVy?=

I am having difficulty adding criteria to a nested series of left joins
I want to do this in SQL as the specifics are altered on the fly.

SELECT DISTINCTROW TblHorses.Name, Sire.Name, Dam.Name
FROM (
tblHorses LEFT JOIN tblHorses AS Sire
ON TblHorses.SireID = Sire.ID
)

LEFT JOIN TblHorses AS Dam
ON TblHorses.DamID = Dam.ID

WHERE dam.color = "brown"
AND sire.color = "brown"

ORDER BY TblHorses.Name;

Another version uses sub-select statements in the FROM clause.

SELECT DISTINCTROW TblHorses.[Name] AS Horse,
Sire.[Name] AS HorsesSire,
Dam.[Name] AS HorsesDam

FROM (
tblHorses LEFT JOIN (
SELECT sire.ID, sire.[Name] FROM tblHorses AS Sire
WHERE sire.color = "brown"
)
ON TblHorses.SireID = Sire.ID
)

LEFT JOIN (
SELECT dam.ID, dam.[Name] FROM TblHorses AS Dam
WHERE dam.color = "brown"
)
ON TblHorses.DamID = Dam.ID

ORDER BY TblHorses.Name;

(I am not completely sure of the legality of the second one; you may need
to move where the aliases are defined outside the brackets). I strongly
suspect that the db engine will use the same query path for both of
these, but it would be interesting to benchmark them formally.

By the way, there is very little point in posting illegal queries and
asking for help. You won't win many friends when they have to debug
errors that you introduced by retyping into your newsreader. It's
preferable by far, as well as quicker for you, to copy-and-paste the
original code.

All the best


Tim F

 

[Guest]

Tim-
THANKS for all your help...
Thought you might find this note by Jeff Mason
interesting: helped a lot with the join exercise and seems to address your
ALIASING legality comment: see <====my comments

THANKS to Jeff

Here is a description of how a SELECT clause works. It's important to
realize that no self respecting query processor would actually do things the
way I describe, but it is helpful to think of things as happening this way:

1. The first thing is that a temporary intermediate table is constructed
which is the result of all the rows resulting from all the JOINS in the FROM
clause. Any JOIN predicates (ON clauses) are applied as the rows are
inserted into this intermediate table. Thus, the rows which end up in the
intermediate table are only those rows which meet the ON condition(s).
<=====Multiple on = WHERE type filter

2. If any tables in the FROM clause are given an alias, then from this
point forward, the columns from that table must include the alias given
whenever they are referenced anywhere else in the query. This is because
from this point on, the column values used elsewhere in the query actually
refer to the columns in the intermediate table and not to the columns in the
"base" tables.

3. The conditions in the WHERE clause are now applied to the rows in the
intermediate table. Only those rows for which the entire WHERE clause is true
are retained in the intermediate table. <==========WHERE sorts all!

4. Next, the GROUP BY clause is processed, and all rows in the intermediate
table are combined according to the GROUP BY clause, and one row is retained
for each distinct group. Group aggregate function values are computed at
this time and added as columns to the intermediate table.

5. Next, the HAVING clause is processed, in a manner identical to that for
the WHERE clause. Thus, the difference between the two is that the WHERE
clause operates on individual rows before grouping, whereas the HAVING clause
operates on rows (groups) after the grouping operation takes place.

6. Next, the columns specified in the SELECT clause are retained from the
intermediate table, and the other columns are discarded. Any aliases
assigned to columns come into existence at this time, all at once. This is
why you can't refer to SELECT clause aliases in a WHERE clause - the WHERE
clause is processed before those aliases come into existence. The resultant
columns comprise the query's resultset. <===========Part of Your ALAISing
question

7. If there is an ORDER BY clause, the resultset is ordered according to
that clause.

Hope this helps...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

=?Utf-8?B?VmFjYXRpb24ncyBPdmVy?=

I am having difficulty adding criteria to a nested series of left joins
I want to do this in SQL as the specifics are altered on the fly.

SELECT DISTINCTROW TblHorses.Name, Sire.Name, Dam.Name
FROM (
tblHorses LEFT JOIN tblHorses AS Sire
ON TblHorses.SireID = Sire.ID
)

LEFT JOIN TblHorses AS Dam
ON TblHorses.DamID = Dam.ID

WHERE dam.color = "brown"
AND sire.color = "brown"

ORDER BY TblHorses.Name;

Another version uses sub-select statements in the FROM clause.

SELECT DISTINCTROW TblHorses.[Name] AS Horse,
Sire.[Name] AS HorsesSire,
Dam.[Name] AS HorsesDam

FROM (
tblHorses LEFT JOIN (
SELECT sire.ID, sire.[Name] FROM tblHorses AS Sire
WHERE sire.color = "brown"
)
ON TblHorses.SireID = Sire.ID
)

LEFT JOIN (
SELECT dam.ID, dam.[Name] FROM TblHorses AS Dam
WHERE dam.color = "brown"
)
ON TblHorses.DamID = Dam.ID

ORDER BY TblHorses.Name;

(I am not completely sure of the legality of the second one; you may need
to move where the aliases are defined outside the brackets). I strongly
suspect that the db engine will use the same query path for both of
these, but it would be interesting to benchmark them formally.

By the way, there is very little point in posting illegal queries and
asking for help. You won't win many friends when they have to debug
errors that you introduced by retyping into your newsreader. It's
preferable by far, as well as quicker for you, to copy-and-paste the
original code.

All the best


Tim F

 

[Tim Ferguson]

=?Utf-8?B?VmFjYXRpb24ncyBPdmVy?=

2. If any tables in the FROM clause are given an alias, then from this
point forward, the columns from that table must include the alias given
whenever they are referenced anywhere else in the query.

That's not true: this runs perfectly happily in Access/Jet:

SELECT RecordID, FName, a.RecordDate
FROM TimeClocks AS a
ORDER BY a.RecordID, RecordDate

so you can mix aliased and unaliased names before and after it's defined.

I don't know the source of the rest of your posting. It seems very
implementation-specific and seems unlikely to be true for every version
of Jet, and even more unlikely to be appropriate for other databases.
Beside which, there is only a loose correlation between what is _legal_
syntax and what is allowed by the interpreter.

Best wishes


Tim F