Ian, Shouldn't the same technique work, where you do separate outer joins against each of those other tables? I only know Oracle SQL, so I never know how it would work in a query of queries (if outer joins are even an option), but I would do this in pseudocode:
qTable1 SELECT Letters FROM Table1 WHERE KEY >= Variables.MinValue AND KEY <= Variables.MaxValue qTable2 SELECT Colors FROM Table2 WHERE KEY >= Variables.MinValue AND KEY <= Variables.MaxValue qTable3 SELECT Names FROM Table3 WHERE KEY >= Variables.MinValue AND KEY <= Variables.MaxValue qRange (This is the dynamically generated one that has qRange.KEY from Variables.MinValue to Variables.MaxValue) qJoin SELECT qRange.KEY, qTable1.Letters, qTable2.Colors, qTable3.Names FROM qRange, qTable1, qTable2, qTable3 WHERE qRange.KEY = qTable1.KEY (+) AND qRange.KEY = qTable2.KEY (+) AND qRange.KEY = qTable3.KEY (+) ORDER BY qRange.KEY You should get NULLs everywhere where there is no match, but the rest should work out. Again, though, I can't remember if this is allowed in Q of Qs. Matthieu That would probably work to solve the problem as I presented it. But the problem I was trying to solve is that I join several tables against the key value and I need them to show up whether the first value exists or not. Trouble is that I can not guarantee any table in the set will have a complete range of values to join against. A more complete example: Table1 Key Letters --- ----- 1 A 3 B 5 C Table2 Key Colors --- ------ 1 Blue 2 Green 3 Red Table3 Key Names --- ----- 3 Joe 5 Sam The result set I'm looking for with a range between 1 and 5 should look something like this: Key Letters Colors Names --- ------- ------ ----- 1 A Blue NULL 2 NULL Green NULL 3 B Red Joe 4 NULL NULL NULL 5 C NULL Sam ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223066 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
