CODEWITHSUNDEEP
mysqlsql
Norman
Norman

Reputation: 6365

Return boolen from two counts on same table

From the same table, what would be a good way to do two different selects and generate a Boolean based on the results.

testable

pKey | prodStatus
-----------------
1    |   0
2    |   0

I'm trying to do this

select count(pKey) from testable where pKey = 1 and prodStatus = 0
along with
select count(pKey) from testable where pKey = 2 and prodStatus = 0

If both results where 1 then `true` else `false`

Right I do this using php and a lot of code because I've no idea how its done purely in sql and something like this is complete beyond me. How can I do something like this in sql itself?

Upvotes: 0

Views: 78

Answers (4)

Barmar
Barmar

Reputation: 781493

SELECT SUM(pKey = 1) = 1 and SUM(pKey = 2) = 1
FROM testable
WHERE prodStatus = 0

Upvotes: 2

Andrew Lazarus
Andrew Lazarus

Reputation: 19340

SELECT SUM(CASE WHEN pKey = 1 THEN 1 ELSE 0 END) 
     = SUM(CASE WHEN pKey = 2 THEN 1 ELSE 0 END)
FROM testable
WHERE prodStatus = 0

Based on Barmar's answer, except (1) he has AND where I think you want an = and (2) not all DBs allow the implicit conversion of boolean to 1/0. This is more portable, and will work if someone using a different DB lands here with Google.

The query planner may be smart enough to optimize two queries with subselects into just one pass over the table, but I wouldn't bet on it.

[edit] left out the END in first version.

Upvotes: 1

invisal
invisal

Reputation: 11171

Will this work for you?

SELECT (
    select count(pKey) = 1 from testable where pKey = 1 and prodStatus = 0
) AND (
    select count(pKey) = 1 from testable where pKey = 2 and prodStatus = 0
)

Check Demo

Upvotes: 2

Charles Bretana
Charles Bretana

Reputation: 146541

 select Sum(case When (pKey = 1 And prodStatus = 0) Or 
                      (pKey = 2 and prodStatus = 0)
             Then 1 Else 0 End)
 from testable

Upvotes: 1

Related Questions