Question 20248 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql filter column results

I have the following data.

ID heade
100 blue
100 black
100 orange
101 brown
101 green
102 green
102 white
102 yellow
103 yellow
103 blue

Intended results: If I select blue, I want to get 100 and 103. If Select blue and yellow return only 103. if I select blue yellow and white, no results should be returned. Basically I want to get only the PID columns that match all the selected colors.
NB: the IN SQL statement will only give me PID that contains any colors mentioned in the statement. I need my criteria to be met as explained above. Let me have have your thought. I’m open to all suggestions even if the solution will involve a combination of sql query for the initial results, and then a json object to filter as required

I have tried the IN in the WHERE predicate clause but this only gives me PID that match any colors in the statement

Tags:

2

Answers


  1. 0 
    SELECT ID, COUNT(DISTINCT heade) AS num_colours
FROM mytable
WHERE heade IN ('blue', 'white')
GROUP BY ID
HAVING num_colours = 2
    Login or Signup to reply.
  2. 0

    This can be generalised as :

    SELECT ID
FROM your_table
WHERE heade IN ('Color1', 'Colo2', 'Color3'......'Colorn')
GROUP BY ID
HAVING COUNT(DISTINCT heade) = n;

    You can pass the exact colors and their count as ‘n’. Here is a DBFILLDLE demo.

    When you pass three colors :

    SELECT ID
FROM your_table
WHERE heade IN ('blue', 'yellow', 'white')
GROUP BY ID
HAVING COUNT(DISTINCT heade) = 3;

    Outout : Empty.

    When you pass two colors :

    SELECT ID
FROM your_table
WHERE heade IN ('blue', 'yellow')
GROUP BY ID
HAVING COUNT(DISTINCT heade) = 2;

    Output :

    ID
103

    When you pass single color :

    SELECT ID
FROM your_table
WHERE heade IN ('blue')
GROUP BY ID
HAVING COUNT(DISTINCT heade) = 1;

    Output :

    ID
100
103
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search