CODEWITHSUNDEEP
sqlsql-serverstored-procedures
mohamed hafil
mohamed hafil

Reputation: 229

How to retrieve data if not exist with certain condition in SQL Server?

I have 3 values

  1. SecurityLevel - ex:1
  2. ReportName - ex:'TotalSales'
  3. UserID - ex:'faisal.3012'

I have 2 tables:

  1. SecurityLevelDetails
  2. SecurityUserDetails

I want to check the data whether it is already exists or not in SecurityUserDetails. If exist, I want to retrieve that exist record, if not I want to retrieve record from SecurityLevelDetails.

I try to make it as a single query, I can do using if condition. But I don't want to do.

I tried this. I know this is wrong. 

Select 
    ReportHide, RColumnName, RFilterName
From 
    HQWebMatajer.dbo.SecurityLevelDetails sld
Where 
    SecurityLevel = 1 
    and not exists(select top 1 
                       UserID, ReportHide, RColumnName, RFilterName 
                   from 
                       [HQWebMatajer].[dbo].[SecurityUserDetails] 
                   where 
                       [UserID] = 'faisal.3012' 
                       and [ReportName] = 'TotalSales')

It's retrieving a record if it does not exist in SecurityUserDetails. But I want to retrieve the record from SecurityUserDetails if it exists

UPDATED I got the result from below code. But I am trying to make in single query

declare @flags int = 0;
select top 1 @flags=count(*)
from [HQWebMatajer].[dbo].[SecurityUserDetails] 
where [UserID]='faisal.3012' and [ReportName]='TotalSales';

if(@flags>0)
BEGIN
    select top 1 UserID,ReportHide,RColumnName,RFilterName 
    from [HQWebMatajer].[dbo].[SecurityUserDetails] 
    where [UserID]='faisal.3012' and [ReportName]='TotalSales'
END
ELSE
BEGIN
    select SecurityLevel,ReportHide,RColumnName,RFilterName
    From HQWebMatajer.dbo.SecurityLevelDetails sld
    where SecurityLevel=1 and ReportName='TotalSales'
END

Upvotes: 0

Views: 522

Answers (1)

Nick.Mc
Nick.Mc

Reputation: 19194

One way to approach this is with UNION ALL

You have both sides defined and a UNION ALL joins them up

I'll stick together all of the code you have posted so far:

select top 1 UserID,ReportHide,RColumnName,RFilterName 
from [HQWebMatajer].[dbo].[SecurityUserDetails] 
where [UserID]='faisal.3012' and [ReportName]='TotalSales'
UNION ALL
select SecurityLevel,ReportHide,RColumnName,RFilterName
From HQWebMatajer.dbo.SecurityLevelDetails sld
where SecurityLevel=1 
and ReportName='TotalSales'
and not exists(select *
               from 
                   [HQWebMatajer].[dbo].[SecurityUserDetails] 
               where 
                   [UserID] = 'faisal.3012' 
                   and [ReportName] = 'TotalSales')

Upvotes: 1

Related Questions