How to retrieve data from two tables with certain conditions using SQL Server?

Question

I have a two tables called 1.ReportTableDetails and 2.SecurityDetails

ReportTableDetails

ReportName   | ColumnNames | FilterNames
--------------------------------------
Total Sales  | data1,data2 | data1,data2
Branch Sales | data1,data2 | data1,data2

SecurityDetails

SecurityLevel | ReportName   | RColumn     | RFilter
--------------------------------------------------------
     1        | Total Sales  | data1,data2 | data1,data2
     4        | Total Sales  | data1,data2 | data1,data2

I will search the records by SecurityLevel from SecurityDetails. The condition is, if the ReportName is not found in SecurityDetails, it should pick the data from ReportTableDetails

Expecting output

SecurityLevel | ReportName   | RColumnNames| RFilterNames | ColumnNames | FilterNames
---------------------------------------------------------------------------------------
     1        | Total Sales  | data1,data2 | data1,data2  |             |
     1        | Branch Sales |             |              | data1,data2 | data1,data2

What I tried is inner join with SecurityDetails table. it retrieves when the report names are equal. But i want to retrieve which is not in SecurityDetails table also

@mohamedfaisal I answered but I have some question with our output I mentione din my answer that as well — Yashveer Singh
– Yashveer Singh, Commented Feb 19, 2017 at 13:42

SqlZim · Accepted Answer · 2017-02-19 14:01:36Z

1

You can do this with a left join:

declare @SecurityLevel int = 1;
select 
      SecurityLevel = isnull(sd.SecurityLevel,@SecurityLevel)
    , ReportName    = isnull(sd.ReportName, rd.ReportName)
    , RColumnNames  = isnull(sd.RColumn,'')
    , RFilterNames  = isnull(sd.RFilter,'')
    , ColumnNames   = case when sd.RColumn is null 
                        then rd.ColumnNames else '' end
    , FilterNames   = case when sd.RFilter is null 
                        then rd.FilterNames else '' end
from ReportTableDetails as rd
  left join SecurityDetails as sd
    on sd.ReportName = rd.ReportName
      and sd.SecurityLevel = @SecurityLevel

returns:

+-------------+---------------+--------------+--------------+--------------+-------------+
|SecurityLevel|  ReportName   | RColumnNames | RFilterNames | ColumnNames  | FilterNames |
+-------------+---------------+--------------+--------------+--------------+-------------+
|           1 | Total Sales   | data1,data2  | data1,data2  |              |             |
|           1 | Branch Sales  |              |              | data1,data2  | data1,data2 |
+-------------+---------------+--------------+--------------+--------------+-------------+

test setup: http://rextester.com/QGZHLO98381

create table ReportTableDetails (
    ReportName  varchar(64)
  , ColumnNames varchar(64)
  , FilterNames varchar(64)
  );
insert into ReportTableDetails values
    ('Total Sales  ','data1,data2 ','data1,data2')
  , ('Branch Sales ','data1,data2 ','data1,data2');
create table SecurityDetails (
    SecurityLevel int
  , ReportName    varchar(64)
  , RColumn       varchar(64)
  , RFilter       varchar(64)
  );
insert into SecurityDetails values
    (1 ,'Total Sales  ','data1,data2 ','data1,data2')
  , (4 ,'Total Sales  ','data1,data2 ','data1,data2');
/* ---------------------------------------------------*/
declare @SecurityLevel int = 1;
select 
      SecurityLevel = isnull(sd.SecurityLevel,@SecurityLevel)
    , ReportName    = isnull(sd.ReportName, rd.ReportName)
    , RColumnNames  = isnull(sd.RColumn,'')
    , RFilterNames  = isnull(sd.RFilter,'')
    , ColumnNames   = case when sd.RColumn is null 
                        then rd.ColumnNames else '' end
    , FilterNames   = case when sd.RFilter is null 
                        then rd.FilterNames else '' end
from ReportTableDetails as rd
  left join SecurityDetails as sd
    on sd.ReportName = rd.ReportName
      and sd.SecurityLevel = @SecurityLevel

edited Feb 19, 2017 at 14:01

answered Feb 19, 2017 at 13:41

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mohamed faisal Over a year ago

Actually when i execute your query, it's retrieving only one row, which is equal the report name from both table

SqlZim Over a year ago

@mohamedfaisal Then you probably changed the query to use where sd.SecurityLevel = @SecurityLevel instead of leaving it as a join condition. Check the rextester link, it proves the query works for the provided test data. rextester.com/QGZHLO98381

mohamed faisal Over a year ago

sorry. its working fine. But i don't want the columname and filternames values of first row record

SqlZim Over a year ago

@mohamedfaisal Updated to return empty strings when SecurityDetails has column and filter values.

Gordon Linoff · Accepted Answer · 2017-02-19 13:20:23Z

0

This is compicated, because you want the security level even when there is no match.

I think the following does what you want:

select sl.SecurityLevel, rd.ReportName,
       sd.RColumnNames, sd.RFilterNames,
       (case when sd.SecurityLevel then rd.columnNames end) as columnMames,
       (case when sd.SecurityLevel then rd.FilterNames end) as FilterNames
from (select distinct sd.SecurityLevel from SecurityDetails sd where  sd.SecurityLevel = 1) sl cross join
     reportdetails rd left join                  
     on sd.ReportName = rd.ReportName and
        sd.SecurityLevel = sl.SecurityLevel;

The sl subquery looks strange (it returns only one row). But this is generalizable to more security levels.

edited Feb 19, 2017 at 13:20

answered Feb 19, 2017 at 13:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

How to retrieve data from two tables with certain conditions using SQL Server?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related