All Questions
16 questions
1
vote
0
answers
457
views
Postgres: one-to-one join with duplicates and inequality matching
** Update: Fiddles now included https://dbfiddle.uk/?rdbms=postgres_14&fiddle=0c618e64c7fcd54c8781ed7d1ac540f0 and https://sqlfiddle.com/#!17/12053 **
I have two tables taken from two data sources,...
- 11
0
votes
2
answers
73
views
Independent aggregation of related entities, while also sorting by related (in a single statement)
I have a model with books and authors, with a many-to-many relationship between them (because a book can have multiple authors, and an author can write multiple books) through a table which I've named ...
- 101
7
votes
2
answers
10k
views
Does Postgres optimize this JOIN with subquery?
In Postgres 12, I have a table purchase_orders and one for its items. I'm running a query that returns PO's for a given shop and a sum of items ordered on each PO:
SELECT po.id,
SUM(...
- 73
3
votes
1
answer
20k
views
ERROR: subquery must return only one column
I have two tables one is Category and other one is Product.
Table Description are
Category
name
description
parent_id (self referencing key) (can be upto 3 levels)
Product
name
description
type
...
- 133
0
votes
1
answer
58
views
How to rank an aggregation in each newly formed group?
The problem below that I would like to solve sounds quite simple but it is quite difficult for me to arrive at the solution with SQL.
Let's say I have a simplified ERD that looks as follows:
This ...
- 101
0
votes
1
answer
199
views
Problem with How to join with subquery
I'm trying to create a report in Postgresql that shows some columns from some tables with join. But one of them is subquery that to show a customer with the location. My Problem is, I always get ...
- 111
1
vote
3
answers
4k
views
How to apply outer limit offset and filters in the subquery to avoid grouping over the complete table used in subquery in Postgresql
I have legacy tables similar to the following:
employee
------------------------------------
| employee_id | name
------------------------------------
| 1 | David
| 2 ...
- 111
1
vote
2
answers
1k
views
How to use GROUP BY in an efficient way without losing attributes? [closed]
I am working with PostgreSQL.
My idea is to group players who appear more than 3 times in table-a.
table-a = <player_id, year_world, c, d, e>
With the first subquery I get player_id and the ...
- 109
3
votes
1
answer
2k
views
Reuse results from correlated subqueries in same query
I have 4 tables (I actually have lots more that I want to do this with...but this is where I am starting).
distr_catalogs: has many distr_catalog_brands, distr_catalog_system_types
...
- 33
3
votes
2
answers
14k
views
Why does COALESCE function not work for this query?
I am trying to count the number of columns that is retrieved from the subquery, and some subqueries may be empty. So instead of displaying it as an empty cell, I want it to be 0. Found out that I can ...
- 33
2
votes
1
answer
5k
views
How to limit an inner query by outer query attribute
I have a requirement to sort a table by multiple criteria, select the first and associate with another table. The DB is Postgres.
Let's say we have players and teams where player has played from ...
- 351
0
votes
3
answers
938
views
Subquery or Inner Join?
I have this simple query that finds all transactions from accounts belonging to a specific login, with some extra conditions on the transactions.
SELECT t.id, t.date, t.amount, t.description FROM ...
- 101
4
votes
1
answer
81
views
PostgreSQL join subquery can't restrict query
I'm working on a check-splitting project that splits out amount owed for an activity. Users can be a part of multiple 'groups', which each have their own running balances.
I'm trying to run a query ...
- 143
2
votes
2
answers
4k
views
Why are correlated subqueries sometimes faster than joins in Postgres?
This relates to the dataset described in Postgres is performing sequential scan instead of index scan
I've started working on adapting the import logic to work with a more normalised schema – no ...
- 291
2
votes
1
answer
3k
views
Left join not working with sub-query
I've got table of scores where each entry corresponds to a particular (student, subject) pair.
CREATE TABLE score (
id serial PRIMARY KEY,
value integer NOT NULL,
subject_id integer NOT NULL,
...
- 1,119
4
votes
1
answer
6k
views
Better way to select from nested tables in PostgreSQL
I have the following schema with millions of rows in the match table and each match have two sides (away and home). I want to create a view which shows the most significant data regarding the matches ...
- 143