Change this query

From: NC <neil100_at_nospambtinternet.com>
Date: Sat, 29 Nov 2003 21:00:14 +0000 (UTC)
Message-ID: <bqb1ct$4u2$1@sparta.btinternet.com>

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 Received on Sat Nov 29 2003 - 15:00:14 CST