MySQLi Search Question (Aliases)

I have a situation where I am pulling all the available colors for a product into the query and I need to include that alias in the search, my query looks something like this:

SELECT p.*, (SELECT GROUP_CONCAT(StandardColor) FROM tbl_products WHERE tbl_products.RootModelNumber = p.RootModelNumber) AS AvailableColors FROM tbl_products AS p etc.

but I get a error Unknown column 'AvailableColors' in 'where clause', I understand that this is a MySQL issue but I wondered if there was any way to get this to work in the search SB?

My search code looks like so:

$searchrsDataSet_1->setSearch(array("type"=>"List", "comparison"=>"Includes", "join"=>"AND"), array("AvailableColors"), "s", "color");

Could you use the query:

SELECT p.*, GROUP_CONCAT(StandardColor) AS AvailableColors FROM tbl_products AS p LEFT OUTER JOIN tbl_products ON tbl_products.RootModelNumber = p.RootModelNumber GROUP BY p.ProductID

and then use the search:

$searchrsDataSet_1->setSearch(array("type"=>"List", "comparison"=>"Includes", "join"=>"AND"), array("tbl_products.StandardColor"), "s", "color");

Thanks Ray, unfortunately that gives me duplicate results on the colors because of the left join so I have hand coded a dynamic HAVING clause for the time being that seems to work

The GROUP BY statement should prevent the multiples... but I'm glad you found a solution that works for you. HAVING is quite a bit slower, but if you don't have too large of a database it shouldn't matter much.

Ray,
Have come back to this today and the issue is that I am grouping by RootModelNumber on the original query not by id so the outer join is pulling all the results. At most the query returns a couple of hundred results and I am paginating so at most 20 per page so it shouldn't be to bad.

UPDATE: I just discovered I can use the keyword DISTINCT within GROUP_CONCAT I had no idea I could do that, this makes it altogether possible now.

2nd UPDATE: although that solves my 1st issue it now removes the other colours when filtering which isn't what I want so back to the HAVING clause again

Thanks