Get more columns from a lookup type subselect

Started by Durumdaraabout 3 years ago4 messagesgeneral

Jump to latest

Durumdara

durumdara@gmail.com

about 3 years ago

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

negora

public@negora.com

about 3 years ago

In reply to: Durumdara (#1)

Re: Get more columns from a lookup type subselect

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.

Show quoted text

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

Durumdara

durumdara@gmail.com

about 3 years ago

In reply to: negora (#2)

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@negora.com> ezt írta (időpont: 2023. márc. 10., P, 9:43):

Show quoted text

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

negora

public@negora.com

about 3 years ago

In reply to: Durumdara (#3)

Re: Get more columns from a lookup type subselect

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;

Show quoted text

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