mysql group concat unknow column error

Question

I am trying to search for query on first name and last name field

here is my query

SELECT d.title, GROUP_CONCAT(u.firstName,' ',u.lastName) as fullname
FROM DEALS d 
left JOIN USER u ON u.idUser = d.userId 
WHERE ((d.title LIKE  '%goutham%' OR d.keywords LIKE  '%goutham%')
OR fullname LIKE  '%goutham%') AND d.isPublic=1

But i got

Unknown column 'fullname' in 'where clause'

Well what is `goutham`? Is it a first name, last name, or both? — Tim Biegeleisen, May 27 '18 at 14:25
Possible duplicate of [Can you use an alias in the WHERE clause in mysql?](https://stackoverflow.com/questions/200200/can-you-use-an-alias-in-the-where-clause-in-mysql) — Paul Spiegel, May 27 '18 at 14:25
Please explain what you want. Since your query is invalid, we can only guess your intention. Best would be to post sample data and expected result. — Paul Spiegel, May 27 '18 at 14:42
@PaulSpiegel I would like to get all the rows deals table that the keyword matches both first name and last name in the user table — shamon shamsudeen, May 27 '18 at 14:44
That is not enough information. Do you want one row per deal or one row per user? Do you need the user names in the result? — Paul Spiegel, May 27 '18 at 14:50

Gordon Linoff · Answer 1 · 2018-05-27T14:40:56.503

You cannot use a column alias in where. It has nothing to do with the rest of your query.

You don't have a GROUP BY, so I suspect GROUP_CONCAT() is not intended. Instead you want CONCAT(). You can repeat the expression in the WHERE, but I think you should look in each component:

SELECT d.title, CONCAT(u.firstName, ' ', u.lastName) as fullname
FROM DEALS d left JOIN USER
     u
     ON u.idUser = d.userId 
WHERE (d.title LIKE '%goutham%' OR
       d.keywords LIKE '%goutham%' OR
       u.firstName LIKE '%goutham%' OR
       u.lastName LIKE '%goutham%'
      ) AND
      d.isPublic = 1;

If you care about performance and are looking for words, then you might want to look into MySQL's full text index capabilities.

If you still want to look on the combination, I would recommend repeating the expression:

WHERE (d.title LIKE '%goutham%' OR
       d.keywords LIKE '%goutham%' OR
       CONCAT(u.firstName, ' ', u.lastName) LIKE '%goutham%'
      ) AND
      d.isPublic = 1;

score 0 · Answer 2 · answered May 27 '18 at 14:30

0

The immediate cause of your problem is that you can't refer to an alias defined in the SELECT clause in the WHERE clause of the same query. The solution would be to repeat the entire expression instead of using the alias. However, based on your comment, you really want to check the first name, so do just that:

SELECT
    d.title,
    CONCAT(u.firstName, ' ', u.lastName) AS fullname
FROM DEALS d 
LEFT JOIN USER u
    ON u.idUser = d.userId 
WHERE
    (d.title LIKE '%goutham%' OR
    d.keywords LIKE '%goutham%' OR
    u.firstName LIKE  '%goutham%') AND d.isPublic = 1;

answered May 27 '18 at 14:30

Tim Biegeleisen

502,043
27
286
360

But if I want to search on the combination like (firstName and lastName) is it possible with this query? – shamon shamsudeen May 27 '18 at 14:35
This doesn't make sense to me. What is `goutham`? Are you looking for a first or last name? Keep in mind that your `fullname` alias is a concatenation with a space of two other fields. – Tim Biegeleisen May 27 '18 at 14:51

mysql group concat unknow column error

2 Answers2