Re: Equilevant of Oracle for optional records

Hi,



SELECT x.*, y.*
FROM ( SELECT * FROM t1 WHERE type="A") AS x
LEFT JOIN
( SELECT * FROM t2 WHERE type="B") AS y
ON x.id = y.id


is what I guess you want. NO record with t1.type <> "A" will appear, but
those with t1.type="A" would either be associated with their counter part in
t2 (sharing a common id) where type="B", either will be associated with
NULLs.


Sure, you can replace the * with explicit lists.

Hoping it may help,
Vanderghast, Access MVP


"Maurice W. Darr" <mdarr@xxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:uAFaj94UGHA.5828@xxxxxxxxxxxxxxxxxxxxxxx
Any or all,

The Oracle syntax for outer joins makes it possible to join to a table by
record type but I cannot find an equilivent in Access. Here's what I mean.

I have two tables
T1 (id)
T2 (id,type,data)
I want a join that will return several records from T2 joined to T1 but
with different Type values
In Oracle this is straigthforward.

Select t1.id,ta.data a_data,tb.data b_data
from t1, t2 ta,t2 tb
where ta.id+=t1.id
and ta.type+="A"
and tb.id+=t1.id
and tb.type+="B"

This query will return all the records in T1. If there is a corresponding
record in T2 with a type of 'A' it will return a record otherwise it will
return null in a_data. The same is true of b_data. Either a null if there
are no records with a 'B' or a value.

In Access I cannot find a way to do this in Access.
It would be nice to use
T1 LEFT JOIN T2 AS Ta ON T1.ID = Ta.CAPID and ta.type='A'

This does not work.
A straight left join with a filter by type will not return any records if
there is at least one alternate type in T2 for the join key.

Any ideas?

TIA, maurice




.

Loading