JOIN on fields where ther may be NULL values

I need to extract data from 4 tables. Each of these
tables contains a field called 'page'. In some instances
this field is null (otherwise an integer).
Here's my problem: I need to join these tables on
the 'id' field and 'page', e.g.
SELECT t1.id, t1.name, t1.date, t2.action, t3.country,
t4.region
FROM table1 t1
LEFT OUTER JOIN table2 t2 on t1.id=t2.id AND
t1.page=t2.page
LEFT OUTER JOIN table3 t3 on t1.id=t3.id AND
t1.page=t3.page
LEFT OUTER JOIN table4 t4 on t1.id=t4.id AND
t1.page=t4.page
WHERE t1.category='questions' AND t1.flag='t'
The query runs and returns data; however, for those rows
where 'page' is null, I do not get any data from tables
t2, t3, t4.
What am I doing wrong?
Thanks,
Rafal
Hi Rafal,
Left Outer Join returns all the records from the table on the Left (in your
case t1) and data that is matching the criteria from the table on the right
(in you case T2 or T3 or T4).
If you want records from both the table, irrespective of whether the
condition matches, you need to use Full Outer Join.
When you use Full Outer Join, data from both the tables is returned, if a
match for the criteria is found then the repective coulmn gets the value
else it will return null.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.