Oracle 8i SQL Help

MattR · #1 (**permalink**) 07-28-03, 11:16

Hi,

I have a table like this:
User_Rating( UserID, Code, Type, Rating )

Sample rows (that I care about) would look like:
12345, 'R', 'RS', '03'
12345, 'R', 'RS', '02'
11111, 'R', 'RS', '03'
12234, 'R', 'PIP', '01'
12234, 'R', 'RS', '03'
22222, 'R', 'PIP', '03'
22222, 'R', 'RS', '01'
44444, 'R', 'PIP', '03'
55555, 'R', 'JOE', '03'
etc.

The situation is this:
I want to get IDs of users who have a code='R' and Rating > 02. However, the Type of 'RS' takes precedence over any other type, so for example in the data above user 22222 would not be matched since RS is less than 02. However, 44444 and 55555 would match since they have an R code which is greater than 02.

So, if there is only one R code I don’t care what the type is. If there is more than one, look at the type of RS and then check the rating.

The way I have it is with a union that grabs all the people with an RS code and rating greater than 02 then unions to the rows that don’t have an RS code:

Code:

SELECT UserID
  FROM User_Rating
 WHERE Code   =  'R'
   AND Type   =  'RS'
   AND Rating >= '03'  
UNION
SELECT UserID
  FROM User_Rating a
 WHERE NOT EXISTS( SELECT *
                     FROM User_Rating b
                    WHERE a.UserID = b.UserID
                      AND b.Code   = 'R'
                      AND b.Type   = 'RS' )
  AND a.Code   =  'R'
  and a.Type   <> 'RS'
  and a.Rating >= '03'

This appears to work, but I’m looking for a more elegant solution. Any thoughts?

Internet Services

Web Development

Digital Marketing

Consumer Tech