CODEWITHSUNDEEP
sqlsql-server
Gazeciarz
Gazeciarz

Reputation: 525

Return rows with userId null or without based on group by

I am using SQL Server 2014. I have following table named: DATE_PARAM:

ID  FROM_VALUE      TO_VALUE                    PARAM_NAME      USER_ID
------------------------------------------------------------------------
1   2016-01-01      2017-01-01 00:00:00.000     date            NULL
2   2016-02-01      2017-01-01 00:00:00.000     date            -1
3   2016-02-01      2018-02-01 00:00:00.000     anotherDate     NULL
4   2016-04-01      2018-02-01 00:00:00.000     anotherDate     3

This table list all available parameters. PARAM_NAME is name of parameter and it is unique per parameter. USER_ID == NULL means this is attribute defined by administrator. USER_ID == any number means that this parameter belongs to user.

What I would like to achieve for example for parameter: userId=-1 - First I group those records by PARAM_NAME. Then in first priority I would like to get attribute which belongs to him (USER_ID=-1) but if there is no attribute which belong to him (within same PARAM_NAME) I would like to get provided by administrator USER_ID=null.

Expected result:

ID  FROM_VALUE      TO_VALUE                    PARAM_NAME      USER_ID
-----------------------------------------------------------------------
2   2016-02-01      2017-01-01 00:00:00.000     date            -1
3   2016-02-01      2018-02-01 00:00:00.000     anotherDate     NULL

I am not sure if it can be realized by query and even if yes if It will be good in performance. I tried do a self left outer join but still do not have expected result.

EDIT - ANSWER

select distinct
    isnull(users.from_value,admin.from_value)  from_, 
    isnull(users.to_value,admin.to_value)  to_,
    param_list.param_name  PARAM_NAME,
    users.user_id USER_ID
from
    (Select param_name from DATE_PARAM) param_list
left join 
    (select * from DATE_PARAM where user_Id = -1) users on users.param_name = param_list.param_name
left join 
    (select * from DATE_PARAM where user_id is null) admin on admin.param_name = param_list.param_name;

Upvotes: 0

Views: 380

Answers (1)

Twelfth
Twelfth

Reputation: 7180

Select param_name from parameters

Above statement assumes you have a parameter table that is simply a list of all parameter. This builds the list of all parameters for you to use as a base. We are then going to left join it twice to this table, once for the user ID and once for the null rows.

...
from ( Select param_name from parameters) PARAM_LIST
left join (select * from tbl where user_Id = -1) users
    on users.param_name = param_list.param_name
left join (select * from tbl where user_id is null) admin
    on admin.param_name = param_list.param_name

This will now give you a row for every parameter and the user value and the admin value. Now it's a matter of using isnull's in your select statement to find the right one.

select isnull(users.from_value,admin.from_value), 
       isnull(users.to_value,admin.to_value),
       param_list.param_name,
       isnull(users.user_id,'admin')

Put it all together

select isnull(users.from_value,admin.from_value), 
       isnull(users.to_value,admin.to_value),
       param_list.param_name,
       isnull(users.user_id,'admin')
from ( Select param_name from parameters) PARAM_LIST
left join (select * from tbl where user_Id = -1) users
    on users.param_name = param_list.param_name
left join (select * from tbl where user_id is null) admin
    on admin.param_name = param_list.param_name

edit... sqlserver is isnull not ifnull...I think

Upvotes: 1

Related Questions