Re: Get more columns from a lookup type subselect

[negora <public@xxxxxxxxxx>]

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