left outer join

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi! i need help on left outer join! when i use two table and with outer join
it works. i want to know if i could use another table (t3) wich i don't need
any outer join, just to import the table where i could use it WHERE
statement. i get an errror msg. i don't know how to import it. i tried before
and after the outer join statement. but its not working. please help. thanks!

select *
from t1
left outer join t2
on t1.name=t2.name
where t1.number = t2.number

this thing is working fine.


select *
from t3, t1
left outer join t2
on t1.name=t2.name
where t1.number = t2.number
t3.number = t1.number
t3.number = t3.number
 
I would TRY adding parentheses to the FROM clause and changing Left Outer
Join to Left Join.

select *
from t3,
(t1
left join t2
on t1.name=t2.name)
where t1.number = t2.number
t3.number = t1.number
t3.number = t3.number

The logic above looks suspect to me. T3.Number is always going to equal
T3.Number unless T3.Number is null. You are T1 and T3 implicitly by using
T3.Number = T1.Number in the WHERE clause.

Also, in Access the Join Type is just LEFT JOIN (versus Left OUTER Join).
 
1. You missed the Boolean AND (???) operators to combine your criteria.

2. The criterion t3.number = t3.number looks suspicious as John wrote.

3. I think the criterion t1.number = t2.number will, in effect, make your
Left Join behaves the same as the Inner Join so you may as well use Inner
Join with 2 Join Conditions which is more efficient than the Left Join on 1
condition and another condition is included in the WHERE clause.

4. Try something like:
========
SELECT *
FROM
(
T1 INNER JOIN T2
ON (t1.name=t2.name) AND (T1.number = T2.number)
), T3
WHERE (T3.number = T1.number)
AND {whatever other criteria}
========
 
Back
Top