barney0o0 Posted March 8, 2016 Share Posted March 8, 2016 Hi, im trying to create a query for search results. The results are formed from two tables that form the full product code (groups.code1.products.codeDX and groups.code1.products.codeSX). What i gave at the mo.. SELECT groups.grid, groups.code1, products.codeDX, products.CodeSX, products.prodid, products.groupid FROM groups LEFT JOIN products ON groups.grid = products.groupid (CONCAT STUFF) WHERE (op1 LIKE \"%$trimm%\" OR op2 LIKE \"%$trimm%\") GROUP BY groups.grid Is it possible to create a CONCAT alias 'after' the join, i.e. CONCAT (code1, ".", codeDX) AS op1 CONCAT (code1, ".", codeSX) AS op2 Its possible that people search for group.code1 (FRSC) or just the CodeSX/CodeDX (100674) or the full product code (FRSC.100674)...the results are then echo'd by groupid....any ideas, or am i looking at the solution from the wrong perspective. thanks in advance Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted March 8, 2016 Solution Share Posted March 8, 2016 You would put it in the SELECT clause. Also note you cannot use aliases in WHERE clauses SELECT groups.grid , groups.code1 , products.codeDX , products.CodeSX , products.prodid , products.groupid , CONCAT (code1, ".", codeDX) AS op1 , CONCAT (code1, ".", codeSX) AS op2 FROM groups LEFT JOIN products ON groups.grid = products.groupid WHERE (CONCAT(code1, ".", codeDX) LIKE '%$trimm%' OR CONCAT(code1, ".", codeSX) LIKE '%$trimm%' ) ORDER BY groups.grid Quote Link to comment Share on other sites More sharing options...
barney0o0 Posted March 9, 2016 Author Share Posted March 9, 2016 Ah, thanks...i didnt realise i could CONCAT 'after' the WHERE clause, hence thinking about aliases...many thanks Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.