LATERAL Derived Table
A LATERAL derived table is a subquery in the FROM clause that can reference preceding JOIN-ed tables (in the same FROM clause.)
Example:
SELECT ... FROM t1 JOIN LATERAL (SELECT * FROM t2 WHERE t2.cx = t1.cy) tl ON ...
Questions tagged [lateral-join]
212 questions
1
vote
1 answer
How to apply a filter on jsonb array of objects - after aggregating?
I have a the follow select statement:
SELECT
cards.*,
COUNT(cards.*) OVER() AS full_count,
p.printing_information
FROM
cards
LEFT JOIN
(SELECT
pr.card_id, jsonb_agg(to_jsonb(pr)) AS printing_information
FROM…
Jake Alsemgeest
- 692
- 2
- 13
- 25
1
vote
0 answers
Looking for an alternative to Postgres's CROSS JOIN LATERAL for Oracle
so my company is moving from Postgres to Oracle and I am moving a query I have in an application over that uses CROSS JOIN LATERAL to combine fields and create a terms list for a auto complete. Below is what I used for Postgres
SELECT
DISTINCT…
dgrambcode
- 11
- 2
1
vote
2 answers
How do I make array dynamic in LATERAL VIEW EXPLODE?
I have a bunch of student records in the table.
school stduent_id start_date end_date
111 123 2010-02-03 2012-02-03
222 345 2013-02-03 2014-02-03
222 567 2015-02-03 2018-02-03
I wanted to count how…
FakeSake
- 23
- 3
1
vote
2 answers
SQL - ordering table by information from multiple tables
Title of the question may not have been very clear - I am not really sure how to name this question, but I hope that my explanation will make my problem clearer.
I have 3 tables:
[1]…
Kleronomas
- 75
- 1
- 1
- 9
1
vote
2 answers
PostgreSQL Left Join with SUM 2 of 4 Table
i'm trying to do LEFT JOIN with SUM 2 of 4 tables, and i'm stuck
In the expected result in the bottom below, there is new field called begin_stock and end_stockresult of daily_stock.qty which is filtered by dates_stat but does not need to use sum()…
Diand
- 878
- 7
- 12
1
vote
2 answers
Get rid of blank result from reg_ex_split_table output
I have a query that counts all of the words in one column and gives me the frequency and frequency rank of the word as the result. For some reason, I keep getting a row that has no word in it. How do I get rid of it?
Table:
CREATE TABLE dummy…
mehsheenman
- 522
- 4
- 11
1
vote
4 answers
Grouping the result set based on conditions
I am calculating Age of a user based on his date of birth.
select UserId, (Convert(int,Convert(char(8),GETDATE(),112))-Convert(char(8),[DateOfBirth],112))/10000 AS [Age] FROM dbo.[User]
This gives me the UserId and his age.
Now I want to group this…
adityaa
- 111
- 2
- 2
- 10
1
vote
2 answers
PostgreSQL LEFT JOIN with SUM & Arithmetic Operators
i'm trying to do LEFT JOIN with SUM and Arithmetic Operators of 3 tables, and i'm stuck,
In the expected result in the bottom below, there is new field called initial_stock which is the result of:
initial_stock = current_stock + sum(used) -…
Diand
- 878
- 7
- 12
1
vote
2 answers
Find closest match to value in another table
I have a table_a with many rows and columns for each timestamp in PostgreSQL 13. I'm trying to find the row where the value in column X is closest to a benchmark value obtained from another table.
This second table has only a single benchmark value…
HH16
- 35
- 6
1
vote
1 answer
How to count result values from join query in another table?
I have two tables like so:
table1(user, id, sex) table2(user, name, sex)
jjj 123 m jjj John m
jjj 124 m bbb Bob m
jjj 125 m ppp Pete f
bbb 126 m
bbb 127 f
ppp…
E. Ron
- 51
- 4
1
vote
1 answer
Making a query that works using one by one values, work for a bunch of values
I have this query that fetches results from a bunch of tables and functions (I use MySQL workbench).
It is like that:
SET @user_name := "any_username";
SELECT @user_id := user_id FROM main_db.user WHERE user_name=@user_name;
SELECT…
escozul
- 99
- 8
1
vote
1 answer
How to group records based on registered time considering entries in another table for the reporting month?
I have a table A with registered time associated for an account , there can be only one entry for each id.
For all the Id's present in Table A there will be entries in Table B like below with state
Expected transformed table
For each ID in table A…
Mozhi
- 757
- 1
- 11
- 28
1
vote
1 answer
Transpose columns in postgresql - lateral join?
I'm trying to transpose a few columns in postgresql.
From this data format:
deal id | deal name | create date | lead_date_pipeline_ | warm_lead_date_pipeline_ | d.meeting_1_date_pipeline_ | d.final_meeting_date_pipeline_ |…
Giuseppe Giobbe
- 23
- 7
1
vote
1 answer
Count matching words between two strings in Postgres?
I want to sort results based on the number of matching words in two strings (case insensitive). Is there a way given two strings to count the number of matching words that appear in both strings?
Example:
"Red blue black green", "Green Black…
Peter R
- 3,185
- 23
- 43
1
vote
1 answer
How to write a sql script that cursors through a table and inserts into a different table
I am new to sql server i have the following table structure that contains more than a thousand rows.
But for example purposes this is what it would look like
Table…
user14241810