Re: 3 way outer join dilemma

On Tue, 19 Aug 2003 terry(at)ashtonwoodshomes(dot)com wrote:

> Uh, sorry. Explicitly stating t1.fid = X and t2.vid = Y was to imply that,
> but a better example would be:
>
> SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
> FROM t1, t2, t3
> WHERE t1.fid = X
> AND t2.vid = Y
> AND t1.id = t2.id
> AND t3.fid = t1.fid
> AND t3.vid = t2.vid

Is this t1.id = t2.id AND t3.fid = t1.fid AND t3.vid = t2.vid
something that where all three will be true anytime any two are true?
I.e. there's maybe no exact need for the cross from t3 to t2 or whatever?

I.e. if t1.id = t2.id, and t3.fid = t1.fid, then by definition must t3.vid
= t2.vid? If the corresponding row in t3 is empty then we have nulls and
all we're worried about is t1 and t2. Is t2 dependent on t1?

I'm guessing there's no need for t3.vid = t2.vid unless your data got
knackered, in which case:

SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
FROM t1 join t2 on (t1.id = t2.id)
left join t3 on (t1.fid = t3.fid)

should do it. Or can count(t3) be >1 for each row referenced in t2 or
anything odd like that and that's why there's t3.vid = t2.vid???

More questions than answers, I know. :-)