CODEWITHSUNDEEP
mysqlsqlwhere-clause
Shihas
Shihas

Reputation: 814

MySQL column ALIAS in WHERE clause

In this below query I have two tables, search_result and login_details. 

SELECT fullname,
(SELECT MAX(login_time) FROM login_details WHERE reg_id=registration) as login_time 
FROM search_result WHERE hire_work=0;

NOTE: reg_id is a column in login_details and registration is a column in search_result

By executing above query I'm getting fullname and the last login time of all the users in search_result table.

Now my requirement is to fetch all the users along with the last login time who all are not logged in for last 180 days from today. When I add a WHERE condition like login_time > '2018-05-22 18:09:00', it shows error Unknown column login_time blah blah.....

Query: 

SELECT fullname,
(SELECT MAX(login_time) FROM login_details WHERE reg_id=registration) as login_time 
FROM search_result WHERE hire_work=0 AND login_time > '2018-05-22 18:09:00';

Please help me to find exact query. Answers will be appreciated. Thanks

Upvotes: 1

Views: 3935

Answers (2)

fifonik
fifonik

Reputation: 1606

The reason why you cannot use aliases (that you assigning in SELECT part) is that this part is executed after WHERE part. So these aliases are not available when SQL server executes WHERE part.

I would not use queries with HAVING without a reason as they are usually more resource expensive for SQL server.

As you are after users who does NOT log in after specified datetime, you do not need to find MAX login time at all and simple left join could be enough:

SELECT
      sr.fullname
FROM
    search_result AS sr
    LEFT JOIN login_details AS ld ON (
            ld.reg_id = sr.registration
        AND ld.login_time > '2018-05-22 18:09:00'
    )
WHERE
        sr.hire_work = 0
    AND ld.login_time IS NULL
;

If you still need the last login time for such users (that would be before the datetime you specified), you can add another subquery (as in your original query) something like this:

SELECT
      sr.fullname
    , (
        SELECT
            MAX(login_details.login_time)
        FROM
            login_details
        WHERE
            login_details.reg_id = sr.registration
    ) AS last_login_time
FROM
    search_result AS sr
    LEFT JOIN login_details AS ld ON (
            ld.reg_id = sr.registration
        AND ld.login_time > '2018-05-22 18:09:00'
    )
WHERE
        sr.hire_work = 0
    AND ld.login_time IS NULL
;

Upvotes: 1

Maksym Fedorov
Maksym Fedorov

Reputation: 6456

Your query doesn't work, because WHERE statement executes before SELECT statement. You should use HAVING statement to filtering of calculating fields. For example:

SELECT fullname,
  (SELECT MAX(login_time)
   FROM login_details
   WHERE reg_id=registration) AS login_time
FROM search_result
WHERE hire_work=0
GROUP BY fullname
HAVING login_time < '2018-05-22 18:09:00'

Also, subqueries aren't good practice. You can rewrite this query with JOIN

SELECT search_result.fullname,
       MAX(login_details.login_time) AS login_time
FROM search_result
JOIN login_details ON login_details.reg_id=search_result.registration
WHERE search_result.hire_work=0
GROUP BY fullname
HAVING login_time < '2018-05-22 18:09:00'

And.. Instead of a constant date, you can use the following expression: NOW() - INTERVAL 180 DAY. For example:

SELECT search_result.fullname,
       MAX(login_details.login_time) AS login_time
FROM search_result
JOIN login_details ON login_details.reg_id=search_result.registration
WHERE search_result.hire_work=0
GROUP BY fullname
HAVING login_time < NOW() - INTERVAL 180 DAY

Upvotes: 5

Related Questions