>> On Feb 16, 2015, at 4:22 AM, [email protected] >> [firebird-support] <[email protected]> wrote: >> >> we have 2 tables >> For each record in Table1 there are 2 records in Table2. >> >> Table1 (T1) >> == >> 1 >> 2 >> >> >> Table2 (T2) >> >> T2 T1 >> ====== >> 1 1 >> 2 1 >> 3 2 >> 4 2 >> >> What is the cheapest way to get a dataset like below? >> >> T1 T2a T2b >> =========== >> 1 1 2 >> 2 3 4 >> > >Can't guarantee it's the cheapest, but you might try > >select T1.T1, max T2.T1 T2a, max T2.T2 T2B >from T1 > inner join T2 on T1.T1 = T2.T1 > group by T1.T1
I think Ann meant select T1.T1, min(T2.T2) "T2a", max(T2.T2) "T2b" --use quotation marks only if you want case sensitivity or special characters in the names, if not it is better to skip them from T1 join T2 on T1.T1 = T2.T1 group by T1.T1 An alternative to this if there are always two matching records, is: select t1.t1, t2_1.t2 "T2a", t2_2.t2 "T2b" from t1 join t2 t2_1 on t1.t1 = t2_1.t1 join t2 t2_2 on t1.t1 = t2_2.t1 and t2_1.t2 < t2_2.t2 Don't know how these two options compare performancewise, I guess Anns solution is quicker or equally quick. Anns solution will work if the number of matches varies (mine won't), whereas mine more easily extends to include other fields matching t2a and t2b. Needless to say, both Anns and my solution can be modified so that they will fit other situations. For more complex situations, you may want to consider EXECUTE BLOCK. Set
