Skip to main content
9 votes
Accepted

Find groups with exactly one value: COUNT(DISTINCT x) = 1 vs MIN(x) = MAX(x)

The short version is that you should expect MIN(val) = MAX(val) to be better for rowstore queries in all cases and COUNT(DISTINCT val) = 1 to be better for columnstore queries when val is a string ...
Joe Obbish's user avatar
  • 32.5k
8 votes

Selecting ALL records when condition is met for ALL records only

Something like this would probably be the most conventional approach: SELECT Name, Course, Grade FROM myTable a WHERE NOT EXISTS ( SELECT 1 FROM myTable b ...
Justin Cave's user avatar
  • 20.3k
7 votes

Selecting ALL records when condition is met for ALL records only

Or for fun why not use the ALL syntax SELECT s1.Name,s1.Course,s1.Grade FROM dbo.Students s1 WHERE 'NA' = ALL ( SELECT a.grade FROM dbo.Students a WHERE s1.Name = a.Name )
Stephen Morris - Mo64's user avatar
6 votes
Accepted

A better way to write this query?

While your query is valid, there is a lot I would differently. Don't use CaMeL-case names in Postgres if it can be avoided. Your unnamed entity framework may force this nonsense upon you, but I don't ...
Erwin Brandstetter's user avatar
6 votes
Accepted

SQL to handle multiple AND conditions

You can use HAVING clause. For example: If you have a list of institutions that have cred type 1,2,3,4,5,6 you could try something like: select institution_no from table where cred_type in (1,2,3,4,...
Ergest Basha's user avatar
  • 3,935
5 votes
Accepted

Incorrect result of query using JOIN of 4 tables

The problem in your original query is that left outer join Comment C on P.ID = C.IDPost and P.UserMail <> C.Mail Should be left outer join Comment C on P.ID = C.IDPost and U.Mail = C.Mail ...
Martin Smith's user avatar
  • 84.9k
4 votes
Accepted

SQL query to find all parent records where child records are a specific set

The problem we are trying to solve is called exact relational division. It requires an extra condition from the relational division and there are still a lot (too many actually) ways to solve this ...
ypercubeᵀᴹ's user avatar
4 votes
Accepted

Select one column with same value and check another column's value

I only use T-SQL, but I don't think anything used below is specific to SSMS: SELECT DISTINCT Cust_ID FROM Tbl t WHERE Status = 'D' AND NOT EXISTS (SELECT * FROM Tbl WHERE Status <> 'D' AND ...
Andy's user avatar
  • 195
4 votes
Accepted

SELECT rows based on indefinite number of filters

This is a special case of relational-division. Here is an arsenal of query techniques: How to filter SQL results in a has-many-through relation The special difficulty of your case is to filter on ...
Erwin Brandstetter's user avatar
4 votes
Accepted

Using EXCEPT with NOT EXISTS

There are three separate parts to the query. I'll label them so I can reference each more clearly: SELECT DISTINCT cname -- Part A FROM account AS cust_account WHERE NOT EXISTS ( SELECT ...
Michael Green's user avatar
4 votes

Query "all of" across many-to-many relation

You mentioned in a later comment: I have some situations where it will produce upwards of 50 joins That many joins are a game changer. Better base query Normally, the order of joins is mostly ...
Erwin Brandstetter's user avatar
4 votes

Get rows that exist exactly once per day for a given period

select did from foobar2 where date between '2019-01-01' and '2019-01-03' group by did having count(distinct date)=3 and count(date)=3; If it has at least one of each date, but more ...
jjanes's user avatar
  • 40.2k
4 votes

Selecting ALL records when condition is met for ALL records only

First though is: SELECT r.[Name], r.Course, r.Grade FROM Results AS r WHERE NOT EXISTS ( SELECT * FROM Results AS c WHERE c.[Name] = r.[Name] AND c.Grade <> ...
David Spillett's user avatar
3 votes

SELECT rows based on indefinite number of filters

Solution CREATE TYPE tag AS ( tag_name text, tag_value text ); CREATE OR REPLACE FUNCTION games_by_tags(VARIADIC tag_filters tag[]) RETURNS table ( game_id int ) as $$ declare ...
Alexander Bird's user avatar
3 votes
Accepted

SQL query to display names of customers who have purchased all the DVD

You can try something like: select c.Customer_Name from Customer c join ( select Customer_No from DVD_Purchase group by Customer_No having count(distinct DVD_No) = (select ...
irimias's user avatar
  • 1,931
3 votes

Postgres join where foreign table has ALL records

Here are a couple of alternate approaches which don't involve using array_agg. Use the INTERSECT operator against the the sets of people_id returned for each tag: WITH both_tags AS ( SELECT ...
mendosi's user avatar
  • 2,087
3 votes

Postgres join where foreign table has ALL records

Two more ways - that use joins or correlated subqueries - and no GROUP BY: The first uses EXISTS subqueries: select p.id, p.name from people as p where exists (select from tags as t where t....
ypercubeᵀᴹ's user avatar
3 votes

Get rows that exist exactly once per day for a given period

Test table: create table se246714 ( did char(1), dt date ); Test data: insert into se246714 values ('A', '2019-01-01'), ('A', '2019-01-01'), ('A', '2019-01-02'), ('A', '2019-01-03'), ('B'...
Philᵀᴹ's user avatar
  • 31.8k
3 votes
Accepted

Get rows that exist exactly once per day for a given period

This is special case of relational-division. Predicate dates just happen to be in sequence and duplicates are excluded. There are many possible solutions. The best fit depends on the complete use case....
Erwin Brandstetter's user avatar
3 votes

Filter rows of one table with conditions coming from another table

This is a classic Relational Division question, you just need to unpivot the JSON into separate key/value rows SELECT u.* FROM users u WHERE EXISTS (SELECT 1 FROM conditions c LEFT JOIN ...
Charlieface's user avatar
  • 13.3k
2 votes

Select one column with same value and check another column's value

select cust_ID from table group by cust_ID having max(status) = 'd' and min(status) = 'd'
paparazzo's user avatar
  • 5,053
2 votes
Accepted

Select column values linked to exact set of values in another column

Assuming a PRIMARY KEY on (chat_id, user_id) - or a UNIQUE constraint plus NOT NULL on both columns. To allow any number of IDs, use a 1-dimensional array parameter: SELECT chat_id FROM ...
Erwin Brandstetter's user avatar
2 votes

SQL query to find all parent records where child records are a specific set

PostgreSQL specific query SELECT i.id, i.name FROM item i WHERE ARRAY[(10, 1000), (20, 2000)] /* must be sorted! */ = ARRAY(SELECT (type, value) FROM item_detail d ...
joanolo's user avatar
  • 13.5k
2 votes

Postgres join where foreign table has ALL records

Another way with a simple equi-join: select p.id, name from people p join tags on tags.people_id=p.id where tag in ('np','yw') group by p.id, name having count(distinct tag)=2; id | name -: | :--- ...
Jack Douglas's user avatar
2 votes
Accepted

Postgres join where foreign table has ALL records

A small variant on mendosi's answer, which avoids WITH: SELECT * FROM people WHERE id IN ( SELECT people_id FROM tags WHERE tag IN ('np', 'yw') GROUP BY people_id ...
joanolo's user avatar
  • 13.5k
2 votes

Postgres join where foreign table has ALL records

Here we select all people and array-agg the tags. We do it in a single-pass. Then we wrap that in a subselect and find all matches where they have both np, and yw. SELECT people_id, name, array_agg(...
Evan Carroll's user avatar
  • 63.7k

Only top scored, non community-wiki answers of a minimum length are eligible