|Subject||RE: [firebird-support] How to join records|
|Author||Svein Erling Tysvær|
>Thank you Ann, Thank you Set,I suppose you could use a combination of the two suggestions, Josef:
>yes, in my real situation I have to extend the query to other fields from T2 - therefore the second solution seems to be the better one for me.
>Could "Common Table Expressions" or "Derived Tables" bring any advantages?
with tmp(t1, t2a, t2b) as
(select T1.T1, min(T2.T2), max(T2.T2)
join T2 on T1.T1 = T2.T1
group by T1.T1)
select tmp.t1, tmp.t2a, tmp.t2b, <whatever>
left join t2 t2_1 on tmp.t2a = t2_1.t2
left join t2 t2_2 on tmp.t2b = t2_2.t2
and t2a.t2 < t2b.t2 --the 'and' part is only required if there may be only one matching t2.t2
The one advantage that immediately springs to mind, is that this would get a result if for some reason there were one or three matches, not exactly two (though in the case of three matches, only the first and last would be returned). Sometimes it can also make complex or repetitive calculations more readable.
EXECUTE BLOCK RETURNS(T1 INTEGER, T2a INTEGER, T2b INTEGER) as
FOR SELECT T1 FROM T1 INTO :T1 DO
SELECT MIN(T2), MAX(T2)
WHERE T1 = :T1
INTO :T2a, T2b:
WHERE T2 = :T2_a
INTO <other T2a output parameters>;
IF (T2a = T2b) THEN
<all T2b parameters> = NULL;
WHERE T2 = :T2_b
INTO <other T2b output parameters>;
is also an alternative. Admittedly, in your particular example, I don't see much point in using it. However, I do find that EXECUTE BLOCK sometimes make complex queries look a lot simpler, so it is worth considering as an alternative if there's considerably more complexity in your real world case.