CODEWITHSUNDEEP
mysqlsql
Mr. Benbenbenbenben
Mr. Benbenbenbenben

Reputation: 123

Returning value only when the value inside the `in` clause exists

Okay I honestly think my title is incorrect, however that's how i interpreted my problem.

Here are my sample data:

id diagnosis symptom
 1 a          c
 2 a          a
 3 a          b
 6 b          a
 7 b          c

My problem is how am I able to retrieve the diagnosis with only the condition inside the in clause?

Here is my query:

SELECT diagnosis 
FROM vdoc_b_symptom
WHERE symptom IN ('a','c')
GROUP BY diagnosis 
HAVING COUNT(*) = 2;

The output i want to get is only b, however it gave me two outputs. How can i get the a value only from the column "diagnosis" with the conditions for symptom a and c? I'm sorry for my English it was so bad.

enter image description here

Upvotes: 0

Views: 42

Answers (3)

Ryan
Ryan

Reputation: 76

Probably some cleverer way to do this, but the way I understand it, you need the results to 1) have both symptoms 'a 'and 'c' as well as 2) not have any other symptoms. So doing a:

SELECT * 
FROM   vdoc_b_symptom 
WHERE  diagnosis IN (SELECT diagnosis 
                     FROM   vdoc_b_symptom 
                     WHERE  symptom IN ( 'a', 'c' ) 
                     GROUP  BY diagnosis 
                     HAVING Count(*) = 2) 
       AND diagnosis NOT IN (SELECT diagnosis 
                             FROM   vdoc_b_symptom 
                             WHERE  symptom NOT IN ( 'a', 'c' ))

should achieve that. May need to name some tables and qualify the columns, though.

Upvotes: 0

Eric
Eric

Reputation: 3257

Try the query below.

SELECT DISTINCT diagnosis
FROM vdoc_b_symptom v
WHERE NOT EXISTS (
    SELECT 1
    FROM vdoc_b_symptom
    WHERE diagnosis = v.diagnosis AND symptom NOT IN ('a', 'c')
)

Upvotes: 1

D-Shih
D-Shih

Reputation: 46229

You can try to use condition in HAVING

Schema (MySQL v5.6)

CREATE TABLE vdoc_b_symptom(
  diagnosis varchar(5),
  symptom varchar(5)
);

insert into vdoc_b_symptom values ('a','a');
insert into vdoc_b_symptom values ('a','b');
insert into vdoc_b_symptom values ('a','c');
insert into vdoc_b_symptom values ('b','a');
insert into vdoc_b_symptom values ('b','c');

Query #1

SELECT 
        diagnosis
    FROM 
        vdoc_b_symptom
    GROUP BY 
          diagnosis
    HAVING 
        COUNT(distinct symptom) = 2 
    AND 
        SUM(symptom = 'a') > 0 
    AND 
        SUM(symptom = 'c') > 0;

| diagnosis |
| --------- |
| b         |

View on DB Fiddle

Upvotes: 2

Related Questions