List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 19 1999 6:52am
Subject:Re: simple query, using as , having problems.
View as plain text  
On Sat, 1999-09-18 22:35:52 +0000, Leonardo Madrigal wrote:
> i make womething like this.
[slightly reformatted]
  SELECT login,crypt,uid,id,shell
  FROM radius
  WHERE user="%user"
    AND (service="something" or service="prepaid")
> works fine....
> but in the prepaid service i want to check first if you already use 
> your hours and decide to let you enter or otherwise....
> 
> i have another table called usertime, in there i can isue something 
> like this
[slightly reformatted]
  SELECT SUM(time_on)
  FROM usertime
  WHERE name="leo"
> that will return my time consumed....
> 
> i want, in only one query to check something like this...
> 
> 
> select 
> radius.login,radius.uid,radius.gid,radius.home,sum(usertime.time_on)/
> 3600 as hours from radius,usertime where radius.login="someuser" and 
> radius.login=radius.name and (service="Internet" or service="Prepaid" 
> and (here goes something for checking if you have more than x 
> hours....) group by radius.login
> 
> since i can not use an alias on a where statement and i cant use 
> having because that will affect non prepaid  users....how can i make 
> the query????

I don't understand your argument on HAVING ... Waht do you mean with
"will affect"?  What's wrong with HAVING used like this:

  SELECT r.login, r.uid, u.gid, r.home, SUM(u.time_on)/3600 AS hours
  FROM   radius AS r, usertime AS u
  WHERE  r.login='someuser'
    AND  r.service IN ('Internet', 'Prepaid')
    AND  r.login=u.name  /* or r.user=u.name ? */
  GROUP BY r.login
  HAVING hours > 5;

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
simple query, using as , having problems.Leonardo Madrigal19 Sep
  • Re: simple query, using as , having problems.Martin Ramsch19 Sep