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?
CodePudding user response:
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.
CodePudding user response:
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
:
About the aggregate FILTER
clause:
Looks like you are running MySQL? Replace the FILTER
clause with:
, count(a.assetid IN (101, 102) OR NULL) AS ct_spec