SQL to return a row where the count of rows in a table joined by a primary key is an exact number

Question

I have 2 tables:

savedoutfits:

outfitid	param
1	a
2	a
etc	etc

accessories:

outfitid	assetid
1	101
1	102
1	103
2	101
2	102
etc	etc

The primary key outfitid in both the savedoutfits table and the accessories table is a table relationship.

I am trying to write an SQL query to grab the outfitid's of a row where savedoutfits.param = 'a' and the linked rows in the accessories table contain the assetid's 101 and 102.

So far I have the following SQL:

SELECT accessories.outfitid, COUNT(accessories.outfitid)
FROM savedoutfits
LEFT JOIN accessories ON savedoutfits.outfitid=accessories.outfitid
WHERE accessories.assetid IN (101, 102)
AND savedoutfits.param='a'
AND accessories.outfitid=savedoutfits.outfitid
GROUP BY accessories.outfitid
HAVING COUNT(DISTINCT accessories.assetid)=2;

This returns both outfitid's 1 and 2 however as shown in the tables above, outfitid 1 has asset id 103.

How can I write the SQL so it only returns rows which match the exact asset id's in the accessories table and no more or no less along with the param column in the savedoutfits table?

Always declare your RDBMS and version, please. – Erwin Brandstetter Aug 22 '22 at 02:03 — Erwin Brandstetter, Aug 22 '22 at 02:03
phpmyadmin version 4.9.7 – user3195474 Aug 22 '22 at 02:42 — user3195474, Aug 22 '22 at 02:42

Tim Biegeleisen · Accepted Answer · 2022-08-22T02:09:53.883

Aggregation can be made to work. We can add another assertion to the HAVING clause which rules out the presence of any assetId other than 101 and 102.

SELECT s.outfitid, COUNT(a.outfitid)
FROM savedoutfits s
INNER JOIN accessories a ON s.outfitid = a.outfitid
WHERE s.param = 'a'
GROUP BY s.outfitid
HAVING COUNT(DISTINCT a.assetid) = 2 AND
       COUNT(CASE WHEN a.assetid NOT IN (101, 102) THEN 1 END) = 0;

Note that I have also switched from left to inner join, which seems to be the logic you want here.

Erwin Brandstetter · Answer 2 · 2022-08-22T03:29:35.003

Tested with Postgres:

SELECT s.outfitid
FROM   savedoutfits s
JOIN  LATERAL (
   SELECT count(*) AS ct_all
        , count(*) FILTER (WHERE a.assetid IN (101, 102)) AS ct_spec
   FROM   accessories a
   WHERE  a.outfitid = s.outfitid
   ) a ON a.ct_all = 2 AND a.ct_spec = 2
WHERE  s.param = 'a';

Assuming accessories(outfitid, assetid) is defined UNIQUE (like seems to be the case).

About LATERAL:

What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

About the aggregate FILTER clause:

Aggregate columns with additional (distinct) filters

Looks like you are running MySQL? Replace the FILTER clause with:

, count(a.assetid IN (101, 102) OR NULL) AS ct_spec

MySQL supports lateral joins, but `FILTER` is pretty unique to Postgres. The above won't run on MySQL. — Tim Biegeleisen, Aug 22 '22 at 02:46

SQL to return a row where the count of rows in a table joined by a primary key is an exact number

2 Answers2