Looking for help to simplify a SQL query

[pennstump]

Hi all. I would appreciate it if you could take a moment to review my SQL code, which is now in the form of 3 statements with temporary views, and point me in the direction of simplifying it into 1 statement.


CREATE TEMP VIEW voidedCounts AS (SELECT pibatch AS batch,count(*) AS counted FROM tags WHERE pibatch IN (SELECT DISTINCT bcbatch FROM batch ORDER BY bcbatch) AND pivoid=true GROUP BY pibatch ORDER BY pibatch);

CREATE TEMP VIEW totalBatchCounts AS (SELECT pibatch AS batch,count(*) AS counted FROM tags WHERE pibatch IN (SELECT DISTINCT bcbatch FROM batch ORDER BY bcbatch) GROUP BY pibatch ORDER BY pibatch);

SELECT voidedBatchCounts.batch FROM voidedBatchCounts INNER JOIN totalBatchCounts ON voidedBatchCounts.batch=totalBatchCounts.batch WHERE (voidedBatchCounts.counted/totalBatchCounts.counted)=1;


Here are the simplified database tables:


CREATE TABLE "batch"
(
"bcbatch" serial,
PRIMARY KEY ("bcbatch")
);

CREATE TABLE "tags"
(
"pitag" serial,
"pibatch" int,
"pivoid" bool,
PRIMARY KEY ("pitag")
);


Essentially, multiple tags exist in a batch and I'm looking to see which batch IDs (pibatch is a foreign key to bcbatch) have 100% of their tags' void flags set to TRUE.

Can someone see a way to get this into 1 statement? Thanks!

 

[PHV]

SELECT pibatch
FROM tags
GROUP BY pibatch
HAVING COUNT(*)=SUM(CASE WHEN pivoid THEN 1 ELSE 0 END)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pennstump]

It does, thank you. Is the CASE statement universal to all/most DBMSs?

 

[r937]

yes, it is universal

CASE is standard SQL (the forum you are in)



r937.com | rudy.ca

 

[Golom]

Except of course for Access where the IIF construct takes that role (... most inelegantly, I might add.)