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
