CODEWITHSUNDEEP
sql
Caner
Caner

Reputation: 59328

Select rows with certain combination of conditions

I have a table of operations similar to one below. Operations either succeed or fail. Sometimes, the operation is not 'tried', then such a row does not exist:

id| Oper. | Success
-------------------
1 |   A   | TRUE
1 |   B   | TRUE
1 |   C   | TRUE
2 |   A   | TRUE
2 |   B   | FALSE
2 |   C   | TRUE
3 |   A   | TRUE
3 |   B   | TRUE
3 |   C   | FALSE
4 |   A   | TRUE
4 |   B   | TRUE
5 |   A   | TRUE
5 |   C   | TRUE
6 |   A   | FALSE
7 |   A   | TRUE
7 |   B   | FALSE
7 |   C   | FALSE

How can I find the id's of the rows from table which have either the operation B & C failed or not tried?

For the example above, the desired result is 2(B failed), 3(C failed), 4(C not tried), 5(B not tried), 6(B & C not tried), 7(both B & C failed)

Upvotes: 0

Views: 308

Answers (6)

Caner
Caner

Reputation: 59328

Thanks for all the answers. With inspiration from the answers and some coffee my brain started to work and came up with the solution that I cannot believe it was this easy.

The solution, in words, is to select if not (b & c succeeded)

The sub-query does the "b & c succeeded" part:

SELECT * FROM table WHERE id NOT IN (
   SELECT id FROM table 
   WHERE operation IN('B', 'C') AND success = true
   GROUP BY id HAVING COUNT(id) = 2
)

Upvotes: 0

Booboo
Booboo

Reputation: 44323

A cross join is not required nor supported by all SQL engines, such as MySQL. The first select statement just selects distinct id's where the operation is either 'B' OR 'C' and success is 'FALSE'. But we need to add those id's that either 'B' or 'C' was never attempted. Those would be id's in the table for which there are no values of either 'B' or 'C' in the operation column.

/* select all id's that have been tried but failed: */
SELECT DISTINCT id FROM operation
    WHERE operation IN ('B', 'C') AND success = 'FALSE'

UNION

/* select all id's that have never tried 'B': */
SELECT DISTINCT id FROM operation
    WHERE id NOT IN (
        SELECT DISTINCT id FROM operation
            WHERE operation = 'B'
    )

UNION

/* select all id's that have never tried 'C': */
SELECT DISTINCT id FROM operation
    WHERE id NOT IN (
        SELECT DISTINCT id FROM operation
            WHERE operation = 'C'
    )
;

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

use aggregation

select id from tbale
group by id
having sum(case when Success='FALSE' then 1 else 0 end)>=1
or count(*)=2

when any of the operations will be failed then sum(case when Success='FALSE' then 1 else 0 end)>=1this condition will be true and return that id, on the other hand when 3rd operation will not be tried then count(*)=2 will be true but in case of 2 operations not tried then condtion of will be count(*)<=2

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74730

I'm assuming that operation A is always present:

SELECT 
  id,
  CASE  
    WHEN b.Success = 'FALSE' THEN '(B failed)'
    WHEN b.Success IS NULL THEN '(B not tried)'
    WHEN c.Success = 'FALSE' THEN '(C failed)'
    WHEN c.Success IS NULL THEN '(C not tried)'
  END as reason
FROM
  (SELECT id, success FROM table WHERE oper = 'A') A

  LEFT JOIN (SELECT id, success FROM table WHERE oper = 'B') B
  ON A.id = B.id

  LEFT JOIN (SELECT id, success FROM table WHERE oper = 'C') C
  ON A.id = C.id

WHERE
  'FALSE' in (c.success, b.success) OR b.success is null or c.success is null

In essence we break your data in 3 virtual tables and left join them, looking for either the false or the null created by the left join when the data doesn't exist. This also allows us to determine why things went wrong. I wasn't sure whether the words in brackets were actual output you wanted or just you explaining why that number should appear in the results.

I considered funky sum/count solutions like Gordon/Zaynul posted but for me this form is more self documenting/readable. Do check the performance though, as indexing the Oper column may help considerably.

Upvotes: 0

DarkRob
DarkRob

Reputation: 3833

You may try this... Please find this link for more info about exists link

  select tab.id, operate.operation from 
  (select distinct id from table) as tab cross join
  (select distinct operation from table ) as operate
  where not exists ( select * from table as table 
  where tab.id=table.id and tab.operate=table.operate and table.success='true' )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

This is tricky, because you want rows that are not in the table.

Generate combinations of all ids and operations. You can do this using a cross join. Then filter out the ones that succeeded:

select i.id, op.operation
from (select distinct id from operations) i cross join
     (select distinct operation from operations) op
where not exists (select 1
                  from operations o2
                  where o2.id = i.id and
                        o2.operation = i.operation and
                        o2.success = 'TRUE'
                 );

Upvotes: 1

Related Questions