SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Joining a table onto itself

    I have a table that looks like this:

    userid - action - date
    10 - added - 2009-10-01
    10 - xled - 2009-10-07
    11 - added - 2009-10-02
    12 - added - 2009-10-02
    23 - xled - 2009-10-09

    I want to return an sql query that will look like this:

    userid - date_added - date_xled
    10 - 2009-10-01 - 2009-10-07

    if users have not canceled it would look like this:
    11 - 2009-10-02 - NULL

    I dont even know where to start??? i'm using php and mysql

    Please help! and thank you in advance!

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,995
    Mentioned
    100 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by laytoneverson View Post
    I have a table that looks like this:

    userid - action - date
    10 - added - 2009-10-01
    10 - xled - 2009-10-07
    11 - added - 2009-10-02
    12 - added - 2009-10-02
    23 - xled - 2009-10-09

    I want to return an sql query that will look like this:

    userid - date_added - date_xled
    10 - 2009-10-01 - 2009-10-07

    if users have not canceled it would look like this:
    11 - 2009-10-02 - NULL

    I dont even know where to start??? i'm using php and mysql

    Please help! and thank you in advance!
    Try something like this:

    Code SQL:
    SELECT
        TABLE_NAME.user_id AS user_id
        , date_added.action_date AS added
        , xled.action_date AS date_xled
    FROM
        TABLE_NAME AS user_action
    INNER JOIN
        TABLE_NAME AS sub
    ON
        xled.parent = date_added.action_date
    ORDER BY
        user_id

    Note, you can't use date as a table or field name as it is a reserved SQL keyword
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    Note, you can't use date as a table or field name as it is a reserved SQL keyword
    To avoid such collisions you can use back ticks (`).

  4. #4
    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)
    Code:
    SELECT userid
         , MAX(added) AS added
         , MAX(xled) AS xled
      FROM ( SELECT userid
                  , CASE WHEN action = 'added'
                         THEN `date` END AS added
                  , CASE WHEN action = 'xled'
                         THEN `date` END AS xled
               FROM daTable ) AS d
    GROUP
        BY userid
    although you really should do cosmetic re-arrangement of query results in your front end language (php), not with SQL

    the query that you should be running is --
    Code:
    SELECT userid
         , added
         , xled
      FROM daTable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,995
    Mentioned
    100 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT userid
         , MAX(added) AS added
         , MAX(xled) AS xled
      FROM ( SELECT userid
                  , CASE WHEN action = 'added'
                         THEN `date` END AS added
                  , CASE WHEN action = 'xled'
                         THEN `date` END AS xled
               FROM daTable ) AS d
    GROUP
        BY userid
    Rudy, is that a bit of an overkill when the OP is basically after a query which joins the table to itself (pages 240-246 of your book)?

    To the OP, the query below should function as you need it to. The only users it will not show is those which have cancelled only, of course there should not be any as your code should not add a 'cancelled' entry if there is no valid 'added' entry

    Code SQL:
    SELECT added.userid, added.date_added, xled.date_xled
    FROM (
    SELECT DATE AS date_added, userid
    FROM daTable
    WHERE 
    ACTION  =  'added'
    ) AS added
    LEFT  OUTER  JOIN (
    SELECT DATE AS date_xled, userid
    FROM daTable
    WHERE 
    ACTION  =  'xled'
    ) AS xled ON added.userid = xled.userid
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  6. #6
    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)
    who said it had to be a self-join?

    and what would you do in a self-join if one of the two actions was missing? a LEFT JOIN might handle one case, but what if it was the other one that was missing? would you do a FULL OUTER JOIN?

    your assumption (that it can't be cancelled if it wasn't added) makes sense, but how do we know for sure?

    and what if, like so many other times people have posted questions, these aren't the "real" actions, but the question has been dumbed down or simplified? (yeah, i know, i always answer the question exactly as asked, so admittedly this is a long shot)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,995
    Mentioned
    100 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    who said it had to be a self-join?

    and what would you do in a self-join if one of the two actions was missing? a LEFT JOIN might handle one case, but what if it was the other one that was missing? would you do a FULL OUTER JOIN?

    your assumption (that it can't be cancelled if it wasn't added) makes sense, but how do we know for sure?

    and what if, like so many other times people have posted questions, these aren't the "real" actions, but the question has been dumbed down or simplified? (yeah, i know, i always answer the question exactly as asked, so admittedly this is a long shot)

    I would use a union query with MySQL not supporting Full Outer Joins to get cases where the there is a xled entry but no coresponding added entry. Chances are that the OP is using MySQL but you can't always rely on the host using MySQL. Though like I said to the OP, there code should check if there is an entry for added before allowing an entry to be made for xled.

    I only went on what the OP asked for, a self-join.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  8. #8
    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)
    Quote Originally Posted by SpacePhoenix View Post
    I only went on what the OP asked for, a self-join.
    i understand

    what if there were 3 or 4 statuses? my query would still make only one pass of the data, but the self-join would begin to explode with all the full-outery possibilities

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict SirAdrian's Avatar
    Join Date
    Jul 2005
    Location
    Kelowna, BC
    Posts
    289
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    FWIW, this is what I came up with:

    Code:
    SELECT userid
         , SUM(IF(action='added', `date`, NULL)) AS date_added
         , SUM(IF(action='xled',  `date`, NULL)) AS date_xled
      FROM yourtable
    GROUP
       BY userid;
    Any comments on it, Rudy?

    Thanks
    Adrian Schneider - Web Developer

  10. #10
    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)
    Quote Originally Posted by SirAdrian View Post
    Any comments on it, Rudy?
    yes, thatnks for asking

    first, are you really summing dates? because i bet the result is not what you expect

    second, i gave you CASE syntax, and you changed it to IF

    CASE is standard SQL, whereas IF is proprietary, non-standard mysql syntax

    use standard SQL whenever you can
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Addict SirAdrian's Avatar
    Join Date
    Jul 2005
    Location
    Kelowna, BC
    Posts
    289
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I came up with this before I saw what everyone else posted. I messed around with joins and ended up with the above query.

    Good to know about the IF versus CASE, I did not know it was not standard. I guess MAX makes more sense in this case... it was just a way to combine the NULL and the date values into one row. My test data used an integer column (unix timestamp), so that could be the cause of the confusion.

    Thanks for the tips!
    Adrian Schneider - Web Developer

  12. #12
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,995
    Mentioned
    100 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i understand

    what if there were 3 or 4 statuses? my query would still make only one pass of the data, but the self-join would begin to explode with all the full-outery possibilities

    In what ways would the self-join begin to "explode"? Would it's performance dergrade or would it start to eat more memory then the other query? A quick test of the two queries shows the self-join to take 0.0007 secs and the other to take 0.0010 secs. Presumably with the other to add another status it would be a case of adding to the query:

    Code SQL:
     , CASE WHEN action = 'the_new_action'
                         THEN `date` END AS the_new_action
    with a corresponding:

    Code SQL:
         , MAX(xled) AS the_new_action
    added to the main part of the query.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  13. #13
    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)
    how would it explode? i'll show you

    why don't you try to write a 4-way self join and allow for unmatched rows for some or all of the statuses
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,995
    Mentioned
    100 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    how would it explode? i'll show you

    why don't you try to write a 4-way self join and allow for unmatched rows for some or all of the statuses
    I've just tried a "4 action" version of both query and the self-join comes in at 0.0017 secs and the other at 0.0007 secs. Now that execution time would start to get painful with a few more "actions" thrown in. What would the memory consumption be like for both?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  15. #15
    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)
    it's not just a question of performance, but of logic

    can i see how you did the 4-way self-join?

    the reason i ask is that there was some quesion about how to do a FULL OUTER JOIN with only two tables (to allow for one status without the other), and i'd like to see your actual SQL for the 4-table version of a FULL OUTER JOIN, to allow for any of the 4 statuses to be missing...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,995
    Mentioned
    100 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    it's not just a question of performance, but of logic

    can i see how you did the 4-way self-join?

    the reason i ask is that there was some quesion about how to do a FULL OUTER JOIN with only two tables (to allow for one status without the other), and i'd like to see your actual SQL for the 4-table version of a FULL OUTER JOIN, to allow for any of the 4 statuses to be missing...

    Code SQL:
    SELECT
        added.userid
        , added.date_added
        , renew.date_renew
        , upgrade.date_upgrade
        , xled.date_xled
    FROM (
        SELECT
            DATE AS date_added
            , userid
        FROM
            sub
        WHERE
            action  =  'added'
    ) AS added
    LEFT  OUTER  JOIN (
        SELECT
            DATE AS date_xled
            , userid
        FROM
            sub
        WHERE
            action  =  'xled'
    ) AS xled ON added.userid = xled.userid
    LEFT  OUTER  JOIN (
        SELECT
            DATE AS date_renew
            , userid
        FROM
            sub
        WHERE
            action  =  'renew'
     
    ) AS renew ON added.userid = renew.userid
    LEFT  OUTER  JOIN (
        SELECT
            DATE AS date_upgrade
            , userid
        FROM
            sub
        WHERE
            action  =  'upgrade'
     
    ) AS upgrade ON added.userid = upgrade.userid

    The reason that I went for joining it each time on added.userid is because I would expect any live web app to never allow any other action entry for a user if a user does not have an added entry.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  17. #17
    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)
    nicely done, but that assumption (that one of the statuses must be there) is the only reason you were able to do that

    what if you did not have that assumption? what if the first action status (the left table in your self-join) was optional?

    the SQL then goes nuts... or, as i said earlier, it begins to explode with all the full-outery possibilities

    in any case, the ~real~ query that should be run here is the one i gave in post #4 --
    Code:
    SELECT userid
         , added
         , xled
      FROM daTable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,995
    Mentioned
    100 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    nicely done, but that assumption (that one of the statuses must be there) is the only reason you were able to do that

    what if you did not have that assumption? what if the first action status (the left table in your self-join) was optional?

    the SQL then goes nuts... or, as i said earlier, it begins to explode with all the full-outery possibilities

    in any case, the ~real~ query that should be run here is the one i gave in post #4 --
    Code:
    SELECT userid
         , added
         , xled
      FROM daTable
    I'll give it a go at the weekend with a query that doesn't assume any particular "action" being in the db. It won't be a full outer join as MySQL doesn't support full outer joins. What's the point in doing it as a full outer when the SQL server that the query might get used on could end up being a MySQL server.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  19. #19
    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)
    Quote Originally Posted by SpacePhoenix View Post
    What's the point in doing it as a full outer when the SQL server that the query might get used on could end up being a MySQL server.
    a full outer join doesn't necessarily have to be accomplished with FULL OUTER JOIN syntax

    you can get the equivalent results with

    SELECT ...
    FROM one LEFT OUTER JOIN two ON ...
    UNION -- note UNION, not UNION ALL
    SELECT ...
    FROM one RIGHT OUTER JOIN two ON ...

    the point is, i think you will find it pretty darned difficult to apply this to 4 tables, and thus come to learn the meaning behind "explode with all the full-outery possibilities"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,995
    Mentioned
    100 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    a full outer join doesn't necessarily have to be accomplished with FULL OUTER JOIN syntax

    you can get the equivalent results with

    SELECT ...
    FROM one LEFT OUTER JOIN two ON ...
    UNION -- note UNION, not UNION ALL
    SELECT ...
    FROM one RIGHT OUTER JOIN two ON ...

    the point is, i think you will find it pretty darned difficult to apply this to 4 tables, and thus come to learn the meaning behind "explode with all the full-outery possibilities"
    After trying it Rudy, I think I see what you mean by "explode with all the full-outery possibilities". Where there is en entry for added it picks up the users but where there isn't it fails to pick up the user:

    Code SQL:
    explode WITH ALL the full-outery possibilities
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator


Tags for this Thread

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
  •