CODEWITHSUNDEEP
sql
user1730177
user1730177

Reputation: 91

Changing sql query output depending on a column value

I have two types of customers in a table, privileged and normal users. When I select some column values from the customers table I want default values for normal users whereas I need actual values for privileged users. query:

select name,id,budget,employees,duration from customers
where reg_date >= to_date( '01-SEP-2012','dd-mmm-yyyy') 
  and reg_date <= to_date('01-OCT-2012','dd-mmm-yyyy')

For normal users I need the values of budget, employees, duration columns as 0.

I have to query another table by 'id' and 'reg_mode' to check if a user is privileged user or not. I may be advised that I should have a column in customers table to include 'type' of the customer. Unfortunately I am not authorized to modify the tables. Any ideas to pull the values with lesser latency?

Upvotes: 0

Views: 3052

Answers (2)

ThaisK
ThaisK

Reputation: 375

Not sure if I got it, specially because the tables structure is not so clear... Lets suppose the other table name is user_type. I suppose what you want is (note my query can have minor errors since I didn't try, I just want to give an idea of the structure):

select name,id, '0' as budget, '0' as employees, '0' as duration from customers 
inner join user_type on
       customers.id = user_type.id
where reg_date >= to_date( '01-SEP-2012','dd-mmm-yyyy')  
  and reg_date <= to_date('01-OCT-2012','dd-mmm-yyyy') 
  and user_type.reg_mode = 'normal'

union

select name,id, budget, employees, as duration from customers 
inner join user_type on
       customers.id = user_type.id
where reg_date >= to_date( '01-SEP-2012','dd-mmm-yyyy')  
  and reg_date <= to_date('01-OCT-2012','dd-mmm-yyyy') 
  and user_type.reg_mode = 'privileged'

Inner join is similar to where, please let me know if you have questions regarding that terminology...

Upvotes: 1

Snowbear
Snowbear

Reputation: 17274

You need to do two things here:

  1. Join your second table which has information about reg_mode:

    SELECT * FROM customers JOIN reg_data ON customers.ID = reg_data.ID

    See details on JOIN here for example

  2. Select correct values depending on reg_mode:

    SELECT (CASE reg_mode WHEN "privileged" THEN customers.budget ELSE 0 END) as budget, ...

    See details on CASE here for SQL server. For other databases it should be more or less the same

Upvotes: 0

Related Questions