score:1
Then convert the query by @GMB into an SQL function that takes an array and returns a table of store_id's.
create or replace
function stores_with_all_labels( label_list text[] )
returns table (store_id text)
language sql
as $$
select store_id
from label_store
where label_id = any (label_list)
group by store_id
having count(*) = array_length(label_list,1);
$$;
Then all that's needed is a simple select. See complete example here.
Read More
- Intersection of Records in Postgres
- select records from postgres where timestamp is in certain range
- Postgres - Function to return the intersection of 2 ARRAYs?
- How to merge all integer arrays from all records into single array in postgres
- Convert all records in postgres to Titlecase, first letter uppercase
- Insert values if records don't already exist in Postgres
score:0
If there are three particular labels you want, you can use:
select store_id
from t
where label in (1, 2, 3)
group by store_id
having count(*) = 3;
If you want only those three labels and nothing else, then:
select store_id
from t
group by store_id
having count(*) = 3 and
count(*) filter (where label in (1, 2, 3)) = count(*);
score:3
Since you're also looking for a jOOQ solution, jOOQ supports a synthetic relational division operator, which produces a more academic approach to relational division, using relational algebra operators only:
// Using jOOQ
T t1 = T.as("t1");
T t2 = T.as("t2");
ctx.select()
.from(t1.divideBy(t2).on(t1.LABEL_ID.eq(t2.LABEL_ID)).returning(t1.STORE_ID).as("t"))
.fetch();
This produces something like the following query:
select t.store_id
from (
select distinct dividend.store_id
from t dividend
where not exists (
select 1
from t t2
where not exists (
select 1
from t t1
where dividend.store_id = t1.store_id
and t1.label_id = t2.label_id
)
)
) t
In plain English:
Get me all the stores (dividend), for which there exists no label (t2) for which that store (dividend) has no entry (t1)
Or in other words
If there was a label (t2) that a store (dividend) does not have (t1), then that store (dividend) would not have all the available labels.
This isn't necessarily more readable or faster than GROUP BY
/ HAVING COUNT(*)
based implementations of relational divisions (as seen in other answers), in fact, the GROUP BY
/ HAVING
based solutions are probably preferrable here, especially since only one table is involved. A future version of jOOQ might use the GROUP BY
/ HAVING
approach, instead: #10450
But in jOOQ, it might be quite convenient to write this way, and you asked for a jOOQ solution :)
score:4
This is a relational division problem, where you want the stores that have all possible labels. Here is an approach using aggregation:
select store_id
from mytable
group by store_id
having count(*) = (select count(distinct label_id) from mytable)
Note that this assumes no duplicate (store_id, label_id)
tuples. Otherwise, you need to change the having
clause to:
having count(distinct label_id) = (select count(distinct label_id) from mytable)
More questions
- How can I SUM distinct records in a Postgres database where there are duplicate records?
- Optimize Postgres deletion of orphaned records
- Postgres WHERE two arrays have a non-empty intersection
- Insert file records into postgres db using clojure jdbc is taking long time compared to python psycopg2
- How do I insert multiple records into my postgres database using Slonik?
- Single Postgres query to update many records using a local hash/array
- Postgres Function to insert multiple records in two tables
- Postgres SQL select a range of records spaced out by a given interval
- Postgres pg_try_advisory_lock blocks all records
- select first 100 rows in a tables of 10000 records and back it up - postgres sql
- Query for records with empty array with Rails and Postgres array type?
- Select (retrieve) all records from multiple schemas using Postgres
- How to query for records less than 48 hours in Postgres using knex.js and objection.js?
- Fast retrieval of records with NULL in postgres
- Version 2: How can I write a postgres sql query that returns M distinct values of a certain column with M + unknown overall records returned?
- Postgres returns records in wrong order
- Postgres LEFT JOIN with SUM, missing records
- How to Update a single column in postgres in a batch for 55 Million records
- How to delete records from CTE (Common Table Expression) in Postgres
- Postgres jsonb array: query for non-empty intersection
- Postgres 10.3 heavily partitioned table and cannot delete any records
- Returning distinct or grouped records from a postgres DB using active record queries
- Postgres server performance falls off a cliff after a certain number of records
- Why does Node.js Postgres Wiki example insert multiple records per http request?
More questions with similar tag
- Problem with Postgresql..DatabaseError: Connection terminated
- Postgresql unique installation id
- column is ambiguous in multiple join postgresql
- How to port a PostgreSQl database to Google App engine
- Django raw SQL queries with ForeignKey
- Laravel data mismatch error while using \PDO::ATTR_EMULATE_PREPARES => true
- Can I use a Postgres keyword as an alias in select list?
- Tuning a query in PostgreSQL
- Executing string queries from function
- Postgres - repeat select over multiple tables and join
- Race condition in partitioning with dynamic table creation
- How are IN expressions executed in SQL queries internally?
- SQL - design for associating list of things with a row
- Order By Id and Limit Offset By Id from a table
- Is this CASE expression in a COALESCE function redundant?
- psql shortcut for frequently used queries? (like Unix "alias")
- No relations/tables found using psycopg2 even after committing
- Querying jsonb in postgresql
- Can a Postgres daterange include infinity as an upper bound?
- Issues with TimeStamp precision at RESTful services
- GROUP BY id HAVING value like 'X' or value like 'Y'
- How to sync the PostgreSQL database script changes with Django back-end
- Postgres JDBC thread stuck on java.net.SocketInputStream.socketRead0 even though timeout was defined
- How develop PostgreSQL extension inluding PostGIS library
- Docker container for Postgres 9.1 not exposing port 5432 to host
- PL/pgSQL: finding all groups person belongs to (also indirectly)
- How to handle duplicates created by LEFT JOIN
- Error passing array of ints to a plpgsql function (postgres 8.3.7)
- JDBC won't update PostgreSQL database
- Join table not working in postgres