SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Member
    Join Date
    May 2004
    Location
    NC
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Alias explanation

    Hi all,

    I'm working on the interface side of a group project and just plain don't understand a query that I have to work with. We're looking for the time difference between events:

    $sql = "SELECT
    x.date AS x,
    y.date AS y
    FROM table
    WHERE x.event = 1 AND y.event = 1
    ORDER BY x.date - y.date DESC;";

    Basically, I don't understand the concept. I do understand basic aliases, but I didn't know that you could put a "x." or a "y." in front of a column name (what is this syntax even called?). Does the x.date and y.date refer to the exact same row?

    If anyone would mind giving an explanation of this, I would really appreciate it. Thanks in advance--

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by chillnc
    ... I didn't know that you could put a "x." or a "y." in front of a column name (what is this syntax even called)
    those are table aliases, and that query has syntax errors because it does not have those tables in it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    May 2004
    Location
    NC
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The query actually runs ok. Are you sure that those are table aliases? We don't have any tables by those names.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    that query runs okay?

    pardon me for being slightly sceptical

    you really have a table called `table`?

    i would like you to post a few sample rows from this table, so that i may try the query myself
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    May 2004
    Location
    NC
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I didn't have a table named 'table' I just made the name generic for posting purposes. But the query does run without error.

    Here are some sample rows:

    ID date event
    43 1085106659 1
    48 1085167702 0
    41 1085027672 0
    40 1085027669 1
    38 1084416017 0
    37 1084415802 0

    thanks-

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by chillnc
    ... the query does run without error.
    i am still as sceptical as before

    Code:
    create table chillnc
    ( id tinyint not null 
    , date integer
    , event tinyint
    );
    
    insert into chillnc values
      ( 43, 1085106659, 1 )
     ,( 48, 1085167702, 0 )
     ,( 41, 1085027672, 0 )
     ,( 40, 1085027669, 1 )
     ,( 38, 1084416017, 0 )
     ,( 37, 1084415802, 0 )
    ;
    
    select id
         , `date`
         , FROM_UNIXTIME(`date`,'%Y-%m-%d %h:%i:%s') 
               as humandate
         , event
     from chillnc
     
    id  date        humandate           event
    43  1085106659  2004-05-20 07:30:59  1
    48  1085167702  2004-05-21 12:28:22  0
    41  1085027672  2004-05-19 09:34:32  0
    40  1085027669  2004-05-19 09:34:29  1
    38  1084416017  2004-05-12 07:40:17  0
    37  1084415802  2004-05-12 07:36:42  0
    
    
    select x.date as x
         , y.date as y 
      from chillnc 
     where x.event = 1 
       and y.event = 1
    order 
        by x.date - y.date desc 
        
    Unknown table 'x' in field list
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    May 2004
    Location
    NC
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm totally at a loss. Let me see if I can figure out why it works for me. thanks for your help.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    good luck

    i expect you will probably find this:
    Code:
    select x.date as x
         , y.date as y 
      from chillnc as x
    inner
      join chillnc as y
     where x.event = 1 
       and y.event = 1
    order 
        by x.date - y.date desc 
    
        
    x          y
    1085106659 1085027669
    1085106659 1085106659
    1085027669 1085027669
    1085027669 1085106659
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    May 2004
    Location
    NC
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, I'm feeling a little stupid. The code I posted above originally existed as this:

    select x.date,
    y.date
    from chillnc as x,
    chillnc as y
    where (x.event = 1) and (y.event = 1)
    order by
    x.date - y.date desc

    I thought I was simply changing the order of the commands (to make more sense to me) but now I realize that I broke it. I didn't realize that it wasn't working because the error was supressed and the function was returning a default value.

    I appreciate all the help you already gave, but could you tell me how the code above works? thanks again-

    BTW: I'm really sorry about wasting your time with the first code I posted.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    hey, no prob

    what you have is a self-join

    the table is joined to itself, and since there are two "copies" of it in the query, you must use table aliases (x and y) to distinguish the columns

    there's no join condition, which means it's a cross join, but only between all rows with event=1

    so basically you join every event=1 row with every other event=1 row (including itself), and then list all the combinations in sequence by date difference

    weird query, and i couldn't begin to tell you what it's for
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Member
    Join Date
    May 2004
    Location
    NC
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the explanantion r937. I'm glad to hear that it's a strange query. It was really bugging me.
    best-
    chillnc


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
  •