KGJ-Dev (1/30/2015)

---

Hi,

Below is the sample data to play with.

declare @users table (IDUser int primary key identity(100,1),name varchar(20),CompanyId int, ClientID int);

declare @Cards table (IdCard int primary key identity(1000,1),cardName varchar(50),cardURL varchar(50));

insert into @users(name,CompanyId,ClientID)

select 'john',1,1 union all

select 'sam',1,1 union all

select 'peter',2,1 union all

select 'james',3,2

Insert into @usercards (IdUser,IdCard,userCardNumber)

select 100,1000,11234556 union all

select 100,1000,11234557 union all

select 100,1001,123222112 union all

select 200,1000,2222222 union all

select 200,1001,2222221 union all

select 200,1001,2222223 union all

select 200,1002,23454323 union all

select 300,1000,23454345 union all

select 300,1003,34543456;

KGJ-Dev (1/30/2015)

---

My Logic:

Declare @FinalData table (IDCard int,CardName varchar(50),CardURL varchar(50),IsUserSpecific bit)

declare @IdUser int = 100, @ClientID int,@companyID int;

select @ClientID = ClientID,@companyID = CompanyId from @users where IDUser = @IdUser;

insert into @FinalData (IDCard,CardName,CardURL,1)

Select distinct c.IdCard,c.cardName,c.cardURL from @usercards UC join @Cards C on(uc.IdCard = c.IdCard)

where IDUser=@IdUser;

if exists(select 1 from @company_cards where @companyID = @companyID)

BEGIN

insert into @FinalData(IDCard,CardName,CardURL,0)

select c.IdCard,c.cardName,c.cardURL from @company_cards cc join @Cards c on(cc.IdCard = c.IdCard) where CompanyId = @companyID

and cc.IdCard not in(select IDCard from @FinalData);

END

ELSE

BEGIN

insert into @FinalData(IDCard,CardName,CardURL,0)

select c.IdCard,c.cardName,c.cardURL from @client_cards cc join @Cards c on(cc.IdCard = c.IdCard) where ClientID = @ClientID

and cc.IdCard not in(select IDCard from @FinalData);

END

select * from @FinalData;

the logic produces the valid result. Is there any alternative way to achieve this logic. I feel there might be some proper way to query this kind of logic. any suggestion please.

[the sample schema and data i provided just to test. i didn't include the index and etc.]