mulitiple table query

[whwar9739]

I have a unique situation. I would like to be able to do a select statement to do some testing. I have 3 tables to join, however the middle table is only a cross reference table and does not always exist. Here is some generic psuedo code to try to explain:

select field1, field2, count(*) 
   from table1 
   join table2 on table2.field1 = table1.field1
   join table3 on table3.field1 = if exists table2.field1 else table1.field1 group by field1, field2

Also look at this post for some more information:
http://www.progresstalk.com/showthread.php?t=114758

 

[tamhas]

Well, for the new folks, this is SQL-89, i.e., in the Progress Editor, not SQL-92 and I still say something about your example does not compute.

First, if table2 doesn't always exist for each table1, doesn't the first join need to be a left outer?

Second, for the second join, you appear to be joining table3.field1 with either table1.field1 or table2.field1, the latter only when table2 exists ... but in the join on the line above you have said that table1.field1 and table2.field1 will be the same when table2 exists, so there is no reason not to join with table1.field1 in all cases.

Finally, I am a bit puzzled by where field2 comes from and how you can have a meaningful count involving a left outer join.