Unknown column in 'where clause' in mysql

Question

I have a query in sql which returns me some rows . Now when i want to search based on some condition ,it says "unknown column in where clause"

SQL Query

SELECT 
  SUM(
    CASE
      WHEN otd.passenger_type = 'adult' 
      THEN 1 
      ELSE 0 
    END
  ) AS 'Adult_count',
  SUM(
    CASE
      WHEN otd.passenger_type = 'child' 
      THEN 1 
      ELSE 0 
    END
  ) AS 'child_count',
  CASE 
    WHEN p.branch = 'EBIZ'
    THEN 'EBIZ'
    ELSE 'LTOB'
   END
    AS 'passenger_branch_new' ,
  t.tour_code AS TOUR_CODE,
  ot.optional_tour_name,
  p.BRANCH,
  tum.login,
  t.Region_name,
  t.start_date ,
  CONCAT(u.first_name,' ',u.last_name) Tname
FROM
  tour t 

  INNER JOIN tour_user_mapping tum 
    ON tum.tour_sub_code = t.tour_sub_code 

     INNER JOIN USER u
  ON u.login = tum.login


  INNER JOIN main_optional_tour_mapping motm 
    ON motm.main_tour_code = t.tour_code 
  INNER JOIN 
    (SELECT 
      tour_code,
      tour_name AS optional_tour_name,
      child_rate,
      adult_rate,
      region 
    FROM
      optional_tour 
    WHERE delete_flag = 'F') AS ot 
    ON ot.tour_code = motm.optional_tour_code 
  INNER JOIN optional_tour_details otd 
    ON otd.optional_tour_code = ot.tour_code 
  INNER JOIN passenger p 
    ON (
      p.passenger_id = otd.passenger_id 
      AND p.tour_code = t.tour_code 
      AND p.tour_sub_code = t.tour_sub_code 
      AND motm.main_tour_code = p.tour_code
    ) 
WHERE t.delete_flag = 'F' 
  AND tum.delete_flag = 'F' 
  AND tum.role_id = '3' 

  AND Tname = 'Sunny Thangaraj'
GROUP BY t.tour_sub_code,
  motm.optional_tour_code,
 passenger_branch_new
ORDER BY t.created_on DESC

Now when i search using Tname i get
Unknown column 'Tname' in 'where clause'
Is there something wrong with syntax ? Please help me with this
Also when i try to search using 'passenger_branch_new' i get the same error of unknown column
But in Group By clause i have used 'passenger_branch_new' and it works fine .

replace `Tname` with `CONCAT(u.first_name,' ',u.last_name)` – RiggsFolly Jan 15 '19 at 12:38 — RiggsFolly, Jan 15 '19 at 12:38

score 1 · Answer 1 · edited Jan 15 '19 at 12:54

1

Instead of Tname use CONCAT(u.first_name,' ',u.last_name) and instead of 'passenger_branch_new' use

CASE 
    WHEN p.branch = 'EBIZ'
    THEN 'EBIZ'
    ELSE 'LTOB'
END

in where clause

edited Jan 15 '19 at 12:54

Vatev

7,493
1
32
39

answered Jan 15 '19 at 12:45

Kedar Limaye

1,041
8
15

Why can't i use alias name ? Tname – Ajinkya Karode Jan 15 '19 at 13:01
Because Tname is not a column name you have derived it from concat operation – Kedar Limaye Jan 15 '19 at 13:03

score 0 · Answer 2 · answered Jan 15 '19 at 12:40

0

You need to use HAVING to filter on a computed result (not a column).

SELECT ...
FROM ...
JOIN ...
JOIN ...
WHERE ...
GROUP BY ...
HAVING Tname = 'Sunny Thangaraj'
ORDER BY ...

answered Jan 15 '19 at 12:40

Vatev

7,493
1
32
39

Unknown column in 'where clause' in mysql

2 Answers2