SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2006
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Correlated Subqueries...Getting my head round them (simply sql CH4)

    Hi guys I have a question on Correlated Subqueries from Simply SQL (CH4).

    The code seems basic enough:

    SELECT
    category
    , title
    , created
    FROM
    entries AS t
    WHERE
    created = (
    SELECT
    MAX(created)
    FROM
    entries
    WHERE
    category = t.category <----------i dont get this point [I know its the correlation variable but why compare 2 same attributes from the same table?]
    )

    My issue is - inside the subquery the WHERE clause is like:

    Where category (from entries table) is equal to t.catergory (from entries table). So is it a case of comparing the same attribute from the same table??

    Any help would be appreciated

    Thanks guys

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,494
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    It's the same table, but you're using it twice. The first time you give it the alias "t", so you can tell SQL which of the two tables you're using in the rest of the query.

    Let's say your entries table contains the following data:
    Code:
    category   title   created
    1          abc     2009-05-01
    1          def     2009-05-29
    2          ghi     2009-05-28
    2          jkl     2009-05-05
    Now, the query takes the first row from the entries table:
    Code:
    category   title   created
    1          abc     2009-05-01
    Then, in the subquery, it'll select the max 'created' from all lines in the entries table that have the same category as the first line selected from the entries table. Which is 2009-05-29. Since the value of 'created' in the first line isn't equal to the max 'created' value for that category, the query won't select this line.
    It then selects the second line, and so on...

    So you see? It's not comparing the same field with itself. It's comparing the field value from the row it selected in the main query with the values of the field in the subquery.

    I hope I made myself clear

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2006
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the description Guido.

    So is it a case of the subquery running through all the data for each iteration of the main query? Sort of like a programming loop??

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,494
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    I don't know how MySQL (or any other database) does it technically speaking, but that's the logic.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    guido, nice explanation
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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