www.webdeveloper.com
Results 1 to 4 of 4

Thread: sub queries question

  1. #1
    Join Date
    May 2010
    Posts
    133

    sub queries question

    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?
    Last edited by anishgiri; 01-13-2013 at 09:58 PM.

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,148
    Is something like this what you are trying to do?
    Code:
    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
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    May 2010
    Posts
    133
    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
    ?
    Last edited by anishgiri; 01-14-2013 at 02:48 AM.

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,148
    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.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles