On 2/8/16, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> On 02/08/2016 11:05 AM, Johannes wrote:
>> Imaging following situation: I want to receive two result sets from two
>> tables, referring to a specific id from table t0 AND I try not to query
>> for that specific id a second time.
>
>> Table t0 returns 1 row and table t1 returns multiple rows.
>>
>> begin;
>> select id, col1, col2, ... from t0 where id = (select max(id) from t0
>> where col1 = value1 and col2 = value2 and ...);
>> select col1 from t1 where t0_id = (select max(id) from t0 where col1 =
>> value1 and col2 = value2 and ...);
>> commit;
>>
>> Best regards Johannes
>
> Based on rough guess of the above, without seeing actual table schemas:
>
> select id, t0.col1, t1.col1, col2, ... from t0 JOIN t1 ON t0.id =
> t1.t0_id where id = (select max(id) from t0 where col1 = value1 and col2
> = value2 and ...);
I don't think it is a good solution because it leads to copying
columns from the t0 which is wasting net traffic and increasing
complexity at the client side. Moreover it works iff t0 returns only
one row.
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
--
Best regards,
Vitaly Burovoy