SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: complex query

  1. #1
    SitePoint Member
    Join Date
    Nov 2004
    Location
    dublin
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question complex query

    Hi,

    I have a table with the following structure:

    name code date
    -----------------------
    paddy 1 just the insert date
    Ian 2 "
    Robert 1 "
    Peter 3 "
    Jane 2 "
    Thomas 3 "

    I need to select a list of the main users ordered alphabetically, who registered themselves the first time with a code.
    And after every main user, his patner, who has the same registration code but registered himself later on.

    Therefore the list will be:

    Ian
    --- jane
    paddy
    --- Robert
    peter
    --- thomas

    The ordering is only alphabetically for the main users. The others are just linked to them.
    Is it posible? How can I do it?

    Thanks a lot. I hope someone can help me.
    Last edited by noradevitt; Nov 25, 2004 at 09:10. Reason: not clear enough

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    how do you distinguish the main users from the partners?

    because a simple join query will give you what you want, except for two things --

    1. it will list them like this:

    Ian jane
    paddy Robert
    peter thomas


    2. it will include the other relationships like this --

    jane Ian
    Robert paddy
    thomas peter
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Nov 2004
    Location
    dublin
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The main user is the one who registered himself first. Therefore, for a code you have two users, the sonner date is the indentifies the main user.
    I can not imagine the query with a join that you mind . I do not see how. Sorry!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select t1.name
         , t2.name
      from yourtable as t1
    inner
      join yourtable as t2
        on t1.code = t2.code
       and t1.date < t2.date
    order
        by t1.name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Nov 2004
    Location
    dublin
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks it is working. The only problem is that I do not get back the users that don't have a patner. I thought doing a UNION with a query that select all the lines where the code only is once in the table. Have to think about it. But thanks anyway!

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select t1.name
         , t2.name
      from yourtable as t1
    inner
      join yourtable as t2
        on t1.code = t2.code
       and t1.date < t2.date
    union all
    select t1.name
         , null
      from yourtable as t1
    inner
      join yourtable as t2
        on t1.code = t2.code
     where t2.code is null
    order
        by t1.name
    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
  •