CODEWITHSUNDEEP
mysqldatabasesubquery
Matter
Matter

Reputation: 449

MySQL different conditions from same column

I'm using a reporting tool for Mac OSX clients called MunkiReport. This tool uses a MySQL database with a table called inventoryitem that looks like this (I left some columns out):

|     id    | serial_number | path
|     1     |     C01       | Applications/Application01.app
|     2     |     C01       | Applications/Application02.app
|     3     |     C01       | Applications/Application03.app
|     4     |     C02       | Applications/Application01.app
|     5     |     C02       | Applications/Application03.app
|     6     |     C02       | Applications/Application04.app

Now let's say I want to list all serial_numbers that have both Application01 and Application03 installed, I do it like this:

SELECT `serial_number`
FROM `inventoryitem`
WHERE `path` IN ('Applications/Application01.app','Applications/Application03.app')
GROUP BY `serial_number`
HAVING COUNT(`path`)=2

Output would be:

| serial_number |
|     C01       |
|     C02       |

But, what I actually want is to list all serial_numbers that DO have Application03 installed but DON'T have Application02 installed...

I've tried several things but I can't get it right. I thought maybe a subquery? First list the Application03 and from that data list all serial_numbers without Application02.

I'm a bit of a rookie with MySQL so it puzzles me a bit.

Upvotes: 0

Views: 48

Answers (3)

user359040
user359040

Reputation:

You could try aggregating conditional expressions in the HAVING clause - like so:

SELECT `serial_number`
FROM `inventoryitem`
WHERE `path` IN ('Applications/Application02.app','Applications/Application03.app')
GROUP BY `serial_number`
HAVING COUNT(case `path` when 'Applications/Application03.app' then 1 end)=1
   AND COUNT(case `path` when 'Applications/Application02.app' then 1 end)=0

Upvotes: 1

Shadow
Shadow

Reputation: 34285

Yes, you can get the desired output using subquery. I would use a NOT EXISTS subquery to achive the desired output:

SELECT DISTINCT `serial_number`
FROM `inventoryitem` i1
WHERE `path`='Applications/Application03.app'
    AND NOT EXISTS (SELECT 1 FROM `inventoryitem` i2 WHERE i2.`path`='Applications/Application02.app' and i2.serial_number=i1.serial_number)

But you can do it with an IN() operator or with a left join as well. But I will leave those example for others or for yourself to figure out.

Upvotes: 1

Ali Tai
Ali Tai

Reputation: 81

JOIN a SELECT statement that grabs all the serial numbers with application 3 installed with a SELECT statement that lists all the serial numbers without application 2 installed (JOIN ON serial_number)

Upvotes: 0

Related Questions