Help needed in Alternate solution

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help needed in Alternate solution

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2015-01-30 : 17:21:43

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));
declare @usercards table (IdUserCard int primary key identity(1,1), IDUser int,IdCard int,userCardNumber bigint);
Declare @company table (CompanyID int primary key identity(1,1),name varchar(50),ClientID int);
Declare @client table (ClientID int primary key identity(1,1),name varchar(50));
Declare @company_cards table (IdcompanyCard int primary key identity(1,1),CompanyId int,IdCard int)
Declare @Client_cards table (IdclientCard int primary key identity(1,1),ClientID int,IdCard int)

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;

insert into @Cards(cardName,cardURL)
select 'BOA','BOA.com' union all 
select 'DCU','DCU.com' union all 
select 'Citizen','Citizen.com' union all 
select 'Citi','Citi.com' union all 
select 'Americal Express','AME.com';

insert into @Client(name)
select 'AMC1' union all 
select 'AMC2'

insert into @company(name,ClientId)
select 'Microsoft',1 union all 
select 'Facebook',1 union all 
select 'Google',2;

insert into @company_cards(CompanyId,IdCard)
select 1,1000 union all 
select 1,1001 union all 
select 1,1002 union all 
select 1,1003 union all 
select 2,1000 union all 
select 2,1001 union all 
select 2,1002;

Requirement :

1. Get the distict Users card details. the reason for using distinct is, user can have same card multiple with different UserCardNumber.

Ex : user can have more than BOA card in the @usercards table with different UserCardNumber. But though he has two BOA card, my query should take one row.

2. After the 1st step, i need to check if any details on @company_cards based on Users companyId.If yes then selct the details from @company_cards. if not select it from @client_cards

In this case we need to make sure that we shouln't have repeated data on @FinalData table.

My Logic:

Declare @FinalData table (IDCard int,CardName varchar(50),CardURL varchar(50))
declare @IdUser int = 100, @ClientID int,@companyID int;

select @ClientID = ClientID,@companyID = CompanyId from @users where IDUser = @IdUser;
insert into @FinalData (IDCard,CardName,CardURL)
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)
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)
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.]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources