sub queries question

[anishgiri]

I am trying to study sub queries, but correlated sub queries is not yet clear to me. Suppose I have users_tbl that have id and user_name column, I also have login_tbl that have id and last_login columns. How can I join the two table,getting the user_name and last_login using sub query? I know how to do this with join. Like this below

SELECT u.user_username, d.date_time_in
FROM users_tbl
JOIN last_login d ON u.user_id = d.user_id

But how to do this using sub query instead?

[NogDog]

Is something like this what you are trying to do?

SELECT
  u.user_username,
  (
    SELECT date_time_in FROM last_login WHERE user_id = u.user_id
    ORDER BY date_time_in DESC LIMIT 1
  ) AS last_time_in
FROM users_tbl AS u

[anishgiri]

I want a condition where only the user id that have record in last_login table will show the username and date_time_in record. So i tried this.

SELECT u.user_username, (

SELECT date_time_in
FROM last_login
WHERE user_id = u.user_id
ORDER BY date_time_in DESC
LIMIT 1
) AS last_time_in
FROM users_tbl AS u
WHERE u.user_id
IN (

SELECT user_id
FROM last_login
)

Now this will display the user_name(with corresponding date_time_in) of the user once. But what i want is if the user has 3 last_login record the query will display his name 3 times also(with corresponding date_time_in) . How to do that with subquery? Also why does it produce the error Subquery returns more than 1 row when I removed ORDER BY date_time_in DESC
LIMIT 1?

[NogDog]

For the last question, since I just wanted one value, without the limit 1 clause, you can get multiple results, which is invalid in that context.

For what you are describing, I would not use a sub-query, just use an inner join.