How to use a dynamically created column in a WHERE clause-CodePudding

I created a table column called name cause the firstname and the lastname are stored in two separate columns. To solve the problem I use CONCAT in the SELECT statement. However if I want to search for a specific value inside the name column SQL returns the error:

Unknown column 'name' in 'where clause'

Is it possible to search in a dynamically created column in a SELECT statement?

SELECT 
    u.ID AS id,
    CONCAT(umFirstName.meta_value, " ", umLastName.meta_value) AS name,
    u.user_email AS email,
    umChatStatus.meta_value AS chatStatus,
    umCallStatus.meta_value AS callStatus,
    umRef.meta_value AS reference
FROM 
    wp_users AS u
LEFT JOIN 
    wp_usermeta AS um ON u.ID = um.user_id
LEFT JOIN 
    wp_usermeta AS umFirstName ON u.ID = umFirstName.user_id 
                               AND umFirstName.meta_key = "_ctbFirstName"
LEFT JOIN 
    wp_usermeta AS umLastName ON u.ID = umLastName.user_id 
                              AND umLastName.meta_key = "_ctbLastName"
LEFT JOIN 
    wp_usermeta AS umChatStatus ON u.ID = umChatStatus.user_id 
                                AND umChatStatus.meta_key = "_ctbChatSessionStatus"
LEFT JOIN 
    wp_usermeta AS umCallStatus ON u.ID = umCallStatus.user_id 
                                AND umCallStatus.meta_key = "_ctbCallSessionStatus" 
LEFT JOIN 
    wp_usermeta AS umRef ON u.ID = umRef.user_id 
                         AND umRef.meta_key = "_ctbRef"
WHERE 
    um.meta_key = "wp_capabilities"
    AND um.meta_value IN ('a:1:{s:8:"employee ";b:1;}') 
    AND u.id LIKE '%%'
     OR name LIKE '%%'
     OR u.user_email LIKE '%%'
     OR umChatStatus.meta_value LIKE '%%'
     OR umCallStatus.meta_value LIKE '%%'
     OR umRef.meta_value LIKE'%%'
ORDER BY 
    id DESC
LIMIT 0, 20

CodePudding user response：

SQL queries are evaluated in the following order (just the blocks you've used):

FROM (including joins)
WHERE
SELECT
ORDER BY

This means that something you create in the SELECT, like CONCAT(Firstname, ' ', LastName) simply doesn't exist at the time the WHERE is evaluated

You have a couple of options:

Use CONCAT(Firstname, ' ', LastName) in the WHERE clause too

SELECT
  CONCAT(Firstname, ' ', LastName) AS N
FROM
  person
WHERE
  CONCAT(Firstname, ' ', LastName) = 'John smith'

Turn the whole query into a sub query and use the thing you created in the outer

SELECT 
  x.N
FROM
  (
    SELECT
      CONCAT(Firstname, ' ', LastName) AS N
    FROM
      person
  ) x
WHERE 
  x.N = 'John smith'

This form can also be written as:

WITH x AS
  (
    SELECT
      CONCAT(Firstname, ' ', LastName) AS N
    FROM
      person
  ) 

SELECT x.N
FROM x
WHERE x.N = 'John smith'

These latter forms are useful when you want to use it multiple times and don't want to keep repeating some massive calculation:

WITH x AS
  (
    SELECT
      SUM(Points) / DATEDIFF(day, MIN(GameStart), MAX(GameEnd)) as PointsPerDay
    FROM
      Games
    GROUP BY PlayerId
  ) 

SELECT 
  CASE 
    WHEN PointsPerDay < 100 THEN 'Newbie'
    WHEN PointsPerDay < 200 THEN 'Seasoned'
    WHEN PointsPerDay < 300 THEN 'Advanced'
    ELSE 'Pro'
  END as Grading
FROM x

To have to keep repeating a calculation in order to do it all in one query is a bit ugly:

SELECT 
  CASE 
    WHEN SUM(Points) / DATEDIFF(day, MIN(GameStart), MAX(GameEnd)) < 100 THEN 'Newbie'
    WHEN SUM(Points) / DATEDIFF(day, MIN(GameStart), MAX(GameEnd)) < 200 THEN 'Seasoned'
    WHEN SUM(Points) / DATEDIFF(day, MIN(GameStart), MAX(GameEnd)) < 300 THEN 'Advanced'
    ELSE 'Pro'
  END as Grading
FROM 
  Games
GROUP BY PlayerId

And indeed because a GROUP BY is evaluated after a WHERE, if you want to use the result of a group by in a where you must do it as a "sub query that groups"/"outer query that wheres" pair

CodePudding user response：

You can directly compare the concatenated value in the where clause like so:
CONCAT(umFirstName.meta_value, " ", umLastName.meta_value) LIKE '