NC wrote:
> Hi,
>
> can anyone suggest a better way of writing this query so that it doesn't use
> the MAX function
>
> SELECT u.user_id FROM uname u WHERE EXISTS ( SELECT r.uid FROM urole r WHERE
> u.uid = r.uid HAVING MAX(r.user_role) < 3 )
>
> to find all uid's in uname that have corresponding entry in user_role but
> with a maximum user_role of less than 3.
> There is no foreign keys constraints between the tables. There is a
> non-unique index on uname(uid) and a unique index on urole(user_role,uid)
>
> SQL> desc uname
>
> Name Null? Type
> ---------------- -------- --------------
> UID INTEGER
>
> SQL> desc urole
>
> Name Null? Type
> ---------------- -------- ----------------------------
> UID NOT NULL INTEGER
> USER_ROLE NOT NULL INTEGER
>
> Cheers
> Neil
>
>
Sounds like homework... lookup group by, and take a good look
at all options listed
And there is a relation (not in the database with a foreign key):
uname.uid <- urole.uid
--
Regards, Frank van Bortel
Received on Sat Nov 29 2003 - 15:19:03 CST
