left outer join

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Theleepan - 06 Dec 2005 16:45 GMT

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

Reply to this Message

John Spencer - 06 Dec 2005 17:09 GMT

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).

> Hi! i need help on left outer join! when i use two table and with outer
> join
[quoted text clipped - 21 lines]
> t3.number = t1.number
> t3.number = t3.number

Reply to this Message

Van T. Dinh - 07 Dec 2005 02:30 GMT

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}
========

Signature

HTH
Van T. Dinh
MVP (Access)

> Hi! i need help on left outer join! when i use two table and with outer
> join
[quoted text clipped - 21 lines]
> t3.number = t1.number
> t3.number = t3.number

Reply to this Message