Hello,
I have this query that returns the chemicals present in a material, among other things:
SELECT
material.material_id,
material.number,
material.name,
onunumber.code,
COUNT(material.material_id) AS Chems
FROM
material
LEFT JOIN onunumber ON onunumber.id = material.onunumber_id
LEFT JOIN material_x_chemical ON material_x_chemical.material_id = material.material_id
WHERE
material.name LIKE '% @CommercialNameQuery %' AND
(@OnlyWithONU = 0 OR onunumber.code IS NOT NULL ) AND
(@MinChemicals = ' ' OR Chems >= @MinChemicals) (doesn't work)
GROUP BY
material.material_id,
material.number,
material.name,
onunumber.code
ORDER BY
@OrderBy
Now, I gotta improve it where I can filter only results with more than a certain number of chemicals. The problem is, I'm getting the chemicals with an Alias as you can see, and I can't run a WHERE Chems > chemicals , or whatever.
I have seen some examples of using subqueries but I'm not quite following. Any help on how to do this?
Note: things with @ are variables coming from a form/table