Re: Get more columns from a lookup type subselect

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 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
>
>