Re: Get more columns from a lookup type subselect

[negora <public@xxxxxxxxxx>]

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@xxxxxxxxxx> 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 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 = 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