CONCAT

[barney0o0]

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

[Barand]

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

[barney0o0]

Ah, thanks...i didnt realise i could CONCAT 'after' the WHERE clause, hence thinking about aliases...many thanks