Gazeciarz Gazeciarz - 3 months ago 8
SQL Question

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;

Answer
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

Comments