Re: Get more columns from a lookup type subselect

For example, if you wanted to list book authors and the latest book of
each one, I would do something like this:

    SELECT
        author.first_name,
        author.surname,
        latest_book.title,
        latest_book.release_date
    FROM author
    CROSS JOIN LATERAL (
        SELECT book.title, book.release_date
        FROM book
        WHERE book.author_id = author.id
        ORDER BY book.release_date DESC
        LIMIT 1
    ) AS latest_book;

On 10/03/2023 11:00, Durumdara wrote:
> Dear Negora!
>
> Can you show me the usage with some short examples?
>
> Thanks for it!
>
> BR
>    dd
>
>
> negora <public(at)negora(dot)com> ezt írta (időpont: 2023. márc. 10., P, 9:43):
>
> Hi Chris:
>
> You can use a subselect in the `from` and `join` clauses. That's
> how I get multiple columns from a single subselect.
>
> If the subselect needs to use a reference from the outer scope
> (i.e. the main query) , you can use the `lateral` modifier.
>
> Best regards.
>
>
> On 10/03/2023 08:34, Durumdara wrote:
>> Dear Members!
>>
>> I use the subselects many times in my Queries to get some info
>> (Name, etc) from a subtable.
>>
>> Sometimes I have to use it to get the last element.
>>
>> select t.*,
>>    (
>>
>> select value from u join ... where ...
>>
>> order by id desc limit 1
>>
>>    ) as last_value,
>>
>> It is ok, but how can I get more values from subselect without
>> repeating the subquery?
>>
>> select t.*,
>>    (
>>
>> select value from u join ... where ...
>>
>> order by date desc limit 1
>>
>>    ) as last_value,
>>    (
>>
>> select type from u join ... where ...
>>
>> order by date desc limit 1
>>
>>    ) as last_type,
>>
>> This is not too comfortable, and may make mistakes if the join is
>> not defined properly or the date has duplicates.
>>
>> Ok, I can use WITH Query:
>>
>> with
>>   pre as ( select * from t .... )
>>   ,sub as (select pre.*, (select u.id <http://u.id> from u where
>> ... limit 1) as last_u_id
>> select  sub.*, u.value, u.type, u.nnn from sub
>> left join u on (u.id <http://u.id> = sub.last_u_id)
>>
>> But sometimes it makes the Query very long (because I have to
>> read more subselects).
>>
>> Do you know a simple method for this, like:
>>
>> select t.*,
>>    (
>>
>> select value, type, anyfield from u join ... where ...
>>
>> order by date desc limit 1
>>
>>    ) as last_value, last_type, anyfield
>>
>> ?
>>
>> Thank you for the help!
>>
>> Best regards
>> Chris
>