Conditional Join - join 1 tables 2 ways
- by Jon H
I have a set of (not very well normalised or relational) tables named
PLAN,
GROUP,
PRODUCT
CLIENT
Most have linkage i.e.
PLAN - CLIENT on clno
GROUP to PRODUCT on PRODCD
However, the linkage between PLAN and GROUP is tricky. A plan has 2 field of interest GRPNO and PRODCD.
What I want to do is if GRPNO != 0 then join GROUP on GRPNO. However if GRPNO = 0 then I want to join GROUP on PRODCD.
The frustrating thing is that the fileds I want to return in my queries are the same across the board I just need to be able to vary the join, or join the same table twice.
The best I can come up with is 2 queries and merge them using datasets, or possibly using a union.
Is there a nifty way to do this in one select?
I should point out I am access Foxpro over ODBC to do this.
Thank you!
