Alias explanation

[chillnc]

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--

[r937]

... 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

[chillnc]

The query actually runs ok. Are you sure that those are table aliases? We don't have any tables by those names.

[r937]

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

[chillnc]

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-

[r937]

... the query does run without error.

i am still as sceptical as before

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

[chillnc]

I'm totally at a loss. Let me see if I can figure out why it works for me. thanks for your help.

[r937]

good luck

i expect you will probably find this:

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

[chillnc]

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.

[r937]

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

[chillnc]

Thanks for the explanantion r937. I'm glad to hear that it's a strange query. It was really bugging me.
best-
chillnc