mulitiple table query

W

whwar9739

Member
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:

Code: 
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
 
Are you doing this in a SQL tool or in the Progress editor. If SQL, you really should ask the question in the SQL forum. If in the Progress editor, you really shouldn't be using SELECT. Yeah, I know, I do it too sometimes when it happens to be quick and easy, e.g., getting a count, but the SQL supported in the Progress editor is SQL 89 ... ancient and deprecated.

Are you simply wanting to do an INNER JOIN?
 
I am doing this in the progress editor. And to me it seems simpler than trying to do it using progress. Unless you can think of a nice way to get the unique sets and counts for a simple test. As far as inner join that is not what I am looking for cause I need all records from table1 and there is a 1 to 1 connection to table3 the only issue is that sometimes the key has to be changed slightly and only when it is changed is there a record from table2. The issue is that in table1, there is a multiple field primary key and in table3 field1 is the primary key.
 
Well, if you had to do something conditional, I actually think it would be easier to do it in ABL than SQL ... but 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.

And, like I say, if you insist on a SQL answer even though you are using the Progress editor (which is a deprecated combination), then it really should be the SQL forum.
 
You must log in or register to reply here.
Back
Top