tuze tuze - 26 days ago 7
MySQL Question

MySQL Data Filter Without Group By

I have created database for my checmical experiments rather than using Excel. I'm filling a HTML form and a php function making some calculations with that data, then insert them to MySQL database table named exp. I don't want to bore you with details. Table design of exp is like that;

example table



Exp ID is the experiment number and Product is for sample taken by in this experiment. Result

What I'm trying to do is filtering this data by both Product and Result. However I don't want to show whole expirement (not only product) If It can't pass the filter.

I'm generating the query dynamically by a HTML form, I can select Product and the Result Number with it's value with a query like that.

Product C, Result5 > 1

SELECT * FROM exp WHERE ExpID IN
(SELECT ExpID FROM exp WHERE Product = 'C' AND Result5 > 1)
ORDER BY ExpID, Product ASC


This query works like a charm. However this query doesn't let me filter two product of same experience. Such as

Product C, Result5 > 1
Product F, Result5 > 0.2


While they are stored in different rows I can't find a proper way to do that. I want all the data of same Experiment in order, so I can't use
group by
. Last of all, there can be more than three product in an experiment, like C1, C2, C3...

Any help would be greatly appreciated. May be a better db design?

Answer

It seems the first step would be to determine which experiment ID's are applicable.

Initially you used this:

SELECT ExpID
FROM exp 
WHERE Product = 'C' AND Result5 > 1

Now you want to these criteria

Product C, Result5 > 1
Product F, Result5 > 0.2 

To get those experiment ID's:

SELECT ExpID
FROM exp 
WHERE (Product = 'C' AND Result5 > 1)
 OR (Product = 'F' AND Result5 > 0.2)
GROUP BY ExpID
HAVING COUNT(*) = 2

The concept being, each experiment must be found exactly (i.e., the HAVING clause) the number of times of each OR criteria.

Ultimately your query is

SELECT * 
FROM exp 
WHERE ExpID IN 
  (SELECT ExpID
    FROM exp 
    WHERE (Product = 'C' AND Result5 > 1)
     OR (Product = 'F' AND Result5 > 0.2)
    GROUP BY ExpID
    HAVING COUNT(*) = 2) 
ORDER BY ExpID, Product ASC

You may get better performance using this

SELECT exp.* 
FROM exp 
JOIN 
    (SELECT ExpID
     FROM exp 
     WHERE (Product = 'C' AND Result5 > 1)
       OR (Product = 'F' AND Result5 > 0.2)
     GROUP BY ExpID
     HAVING COUNT(*) = 2) AS temp
  ON exp.ExpID = temp.ExpID
ORDER BY exp.ExpID, exp.Product ASC
Comments