select query with if in oracle

Question

I need help! For example, there are four tables: cars, users, departments and join_user_department. Last table used for M: N relation between tables user and department because some users have limited access. I need to get the number of cars in departments where user have access. The table “cars” has a column department_id. If the table join_user_department doesn’t have any record by user_id this means that he have access to all departments and select query must be without any condition. I need do something like this:

declare
DEP_NUM  number;--count of departments where user have access
 CARS_COUNT number;--count of cars
BEGIN
SELECT COUNT (*) into DEP_NUM from join_user_departments where user_id=?;
SELECT COUNT(*) into CARS_COUNT FROM cars where 
  IF(num!=0)—it meant that user access is limited
  THEN department_id IN (select dep_id from join_user_departments where user_id=?);

Dan A. · Accepted Answer · 2012-04-09 23:07:37Z

A user either has access to all cars (I'm assuming all cars are tied to a department, and the user has access to all departments) or the user has limited access. You can use a UNION ALL to bring these two groups together, and group by user to do a final count. I've cross joined the users with unlimited access to the cars table to associate them with all cars:

(UPDATED to also count the departments)

select user_id, 
    count(distinct department_id) as dept_count, 
    count(distinct car_id) as car_count,
from (
    select ud.user_id, ud.department_id, c.car_id
    from user_departments ud
    join cars c on c.department_id = ud.department_id
    UNION ALL
    select u.user_id, v.department_id, v.car_id
    from user u
    cross join (
        select d.department_id, c.car_id
        from department d
        join cars c on c.department_id = d.department_id
    ) v
    where not exists (
        select 1 from user_departments ud 
        where ud.user_id = u.user_id
    )
)
group by user_id

A UNION ALL is more efficient that a UNION; a UNION looks for records that fall into both groups and throws out duplicates. Since each user falls into one bucket or another, UNION ALL should do the trick (doing a distinct count in the outer query also rules out duplicates).

APC · Accepted Answer · 2012-04-09 23:03:15Z

1

"If the table join_user_department doesn’t have any record by user_id this means that he have access to all departments"

This seems like very bad practice. Essentially you are using the absence of records to simulate the presence of records. Very messy. What happens if there is a User who has no access to a Car from any Department? Perhaps the current business logic doesn't allow this, but you have a "data model" which won't allow to implement such a scenario without changing your application's logic.

answered Apr 9, 2012 at 23:03

APC

147k20 gold badges180 silver badges289 bronze badges

1 Comment

Dan A. Over a year ago

Good point. There should be a flag in the database that explicitly grants full access for a user.

Collectives™ on Stack Overflow

select query with if in oracle

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related