mysql query using alias throws error

Question

Running the following query

SELECT p.id as pid,p.name as pname, p.email,p.phone,p.created as pcreated,
p.updated as pupdated, GROUP_CONCAT(g.name) AS groups FROM ft_smsender_persons p 
JOIN ft_smsender_persons2groups AS p2g ON p.id = p2g.person_id JOIN ft_smsender_groups AS g ON g.id = p2g.group_id 
WHERE 1=1 AND deleted = 0 AND p.id as pid LIKE '%7%' OR pname LIKE '%7%' OR 
email LIKE '%7%' OR phone LIKE '%7%' OR pcreated LIKE '%7%' 
OR pupdated LIKE '%7%' OR groups LIKE '%7%' group by pname, email ORDER BY pid asc

gives me an error on p.id as pid LIKE '%7%'

If I use it like pid like %7%, it gives me an error saying pid is unknown column

The query is generated dynamically so I have very less scope how to fix it through my code; is there any way in mysql?

if i run it like this SELECT p.id as pid,p.name as pname, p.email,p.phone,p.created as pcreated, p.updated as pupdated, GROUP_CONCAT(g.name) AS groups FROM ft_smsender_persons p JOIN ft_smsender_persons2groups AS p2g ON p.id = p2g.person_id JOIN ft_smsender_groups AS g ON g.id = p2g.group_id WHERE 1=1 AND deleted = 0 AND pid LIKE '%7%' OR pname LIKE '%7%' OR email LIKE '%7%' OR phone LIKE '%7%' OR pcreated LIKE '%7%' OR pupdated LIKE '%7%' OR groups LIKE '%7%' group by pname, email ORDER BY pid asc . The error it gives me is this: [Err] 1054 - Unknown column 'pid' in 'where clause' — John
– John, Commented Aug 12, 2015 at 18:37

A l w a y s S u n n y · Accepted Answer · 2015-08-12 18:44:55Z

2

An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column.

But,

Standard SQL disallows references to column
aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.

Details: https://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html

answered Aug 12, 2015 at 18:44

A l w a y s S u n n y

38.8k9 gold badges69 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BK435 · Accepted Answer · 2015-08-14 02:05:13Z

To clarify what Being Sunny said... You should not use an alias for a column in your where clause part of your query unless it is being referenced in an aggregate function. The query uses the where clause to determine how to narrow down the rows from the tables you have joined, using join criteria, before selecting the columns. Therefore, referring to an aliased column name in the where clause for non-aggregate function will result in an Error 1054: unknown column name.

Try removing those aliases from your query in the where clause:

SELECT p.id as pid, p.name as pname, p.email, p.phone, p.created as pcreated,
p.updated as pupdated, GROUP_CONCAT(g.`name`) AS groups FROM ft_smsender_persons p 
JOIN ft_smsender_persons2groups AS p2g ON p.id = p2g.person_id JOIN ft_smsender_groups AS g ON g.id = p2g.group_id 
WHERE 1=1 AND deleted = 0 AND p.id LIKE '%7%' OR p.`name` LIKE '%7%' OR 
p.email LIKE '%7%' OR p.phone LIKE '%7%' OR p.created LIKE '%7%' 
OR p.updated LIKE '%7%' OR `groups` LIKE '%7%' group by pname, email ORDER BY pid asc;

Harley · Accepted Answer · 2015-08-12 20:14:28Z

0

You cannot have Aliasing in WHERE clause. If this is a dynamically generated query, you can look to change the parameter that tries to give 'p.id' an alias of 'pid'. This would remove the pid aliasing in your result set, but would also remove the error you are getting.

answered Aug 12, 2015 at 20:14

Harley

5625 silver badges8 bronze badges

Collectives™ on Stack Overflow

mysql query using alias throws error

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related