Dear Negora! Can you show me the usage with some short examples?
Thanks for it! BR dd negora <pub...@negora.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 > >
