MySQL: Find all customerIDs with multiple ID Values-CodePudding

I have the following table

customerID | fieldID | fieldValue
1          | 1       | 0
1          | 2       | 8
1          | 3       | Test
1          | 4       | Other Value
2          | 1       | 8
2          | 4       | Testvalue

I want all customerID which have e.g. the following values:

fieldID | fieldValue
1       | 0
2       | 8

I have tried the following:

SELECT customerID
FROM my_table
WHERE
(fieldID = 1 AND fieldValue = 0) AND
(fieldID = 2 AND fieldValue = 8)
GROUP BY customerID

Since I do not get a value, there is surely an error here. How should I proceed here?

CodePudding user response：

You need to change the AND between the parenthesis to OR

SELECT customerID
FROM my_table
WHERE
(fieldID = 1 AND fieldValue = 0) OR
(fieldID = 2 AND fieldValue = 8)
GROUP BY customerID;

CodePudding user response：

I think this is the query you actually want here:

SELECT customerID
FROM my_table
GROUP BY customerID
HAVING SUM(fieldID = 1 AND fieldValue = 2) > 0 AND
       SUM(fieldID = 0 AND fieldValue = 8) > 0;