CODEWITHSUNDEEP
mysql
Michael
Michael

Reputation: 343

MySQl: where comparing values

I have the following MySQL table Test_Results

| ID | Test_ID | Device_OS| Status | Date     | Device_ID |
| 1  | 1       | Android  | passed | 2015/10  | 1         |
| 2  | 1       | Android  | failed | 2015/10  | 1         |
| 3  | 15      | iOS      | failed | 2015/11  | 2         |
| 4  | 20      | Android  | passed | 2015/11  | 1         |
| 5  | 27      | Android  | failed | 2015/11  | 1         |
| 6  | 10      | iOS      | failed | 2015/12  | 2         |
| 7  | 5       | Android  | passed | 2015/12  | 1         |

I need to select the tests that failed on Device with ID = 1, but passed on Device with ID =2.

So I need to compare values based on Device_ID, can you please direct me what to search on the Internet.

Upvotes: 1

Views: 53

Answers (3)

Gouda Elalfy
Gouda Elalfy

Reputation: 7023

select Test_ID, COUNT(Test_ID) as TEST_COUNT from Test_Results 
where (Status='failed' AND Device_ID = 1) OR (Status='passed' AND Device_ID = 2)
group by Test_ID
Having TEST_COUNT>1

Upvotes: 0

tisuchi
tisuchi

Reputation: 129

Just make it in secure way.... 

            SELECT * 
            FROM Test_Results
            WHERE (`Device_ID` = 1 AND `Status` = 'passed') 
                  OR
                  (`Device_ID` = 2 AND `Status` = 'failed');

Hope will work perfectly...

Upvotes: -1

pri
pri

Reputation: 1531

If you want both the conditions to be satisfied, then try this:

SELECT Test_ID
FROM Test_Results
WHERE Test_ID IN (SELECT Test_ID FROM Test_Results WHERE Device_ID = 1 AND Status LIKE 'failed')
AND Device_ID = 2 AND Status LIKE 'passed'

Upvotes: 2

Related Questions