Joins

[rayp0614]

Is there a way to handle left and right outer joins in one shot? Sometimes Table1 has all and Table2 some and sometimes it's the opposite.


Left Outer Join Right Outer Join

Table 1 Table 2 Table 1 Table 2
-----------------------------------------------------
1 1 1 1
2 2 2 2
3 3

 

[r937]

what you're looking for is a full outer join, and i don't know whether access supports it

however, you can always get a full outer join like this:

select foo
  from table1
left outer 
  join table2
    on table1.id = table2.id
union all
select foo
  from table1
right outer 
  join table2
    on table1.id = table2.id
 where table1.id is null

the first select gets all rows of table1, with and without matching rows of table2, while the second gets all rows of table 2 without matching rows of table1

rudy

 

[rayp0614]

Thanks Rudy. FOO is not recognized by Access. Is that a SQL command?

Ray

 

[r937]

foo is "first on the standard list of metasyntactic variables used in syntax examples"

see

http://www.tuxedo.org/~esr/jargon/html/entry/foo.html