2 sets of results from 1 select statement?

[Eric34]

i need to show todays and yesterday's hits to my site from different users in a table:

USER_ID TODAYS HITS YESTERDAYS HITS
-------------------------------------
1023 54 46
1024 122 139
...

currently i am using one select statement to get todays hits for each user and within the while statement i have another sql for yesterdays hits...this is not very nice and i was wondering if there was a better way to do this...


select * from hits where date=$today
...
while ($db->next_record()) {
$id=$db->f('user_id');
select * from hits where date=$yesterday and user_id=$id
...
}

is there a way to have this in a single sql statement? thanks

 

[Vancouverite]

You can reference the same table twice in a query, e.g.:

Select h1.user_id, h1.HitCount As "Today", h2.HitCount As "Yesterday"
From Hits h1
Inner Join Hits h2 on h1.user_id = h2.user_id
WHERE h1."Date" = $Today
AND h2."Date" = $yesterday