Ytukyg

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

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

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

;