January 30, 2015 at 1:05 pm
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),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.]
January 31, 2015 at 11:45 am
Guys, any Suggestion please
January 31, 2015 at 11:52 am
Quick question, is this ERD an accurate interpretation?
😎
+-----------+ +-----------+ +----------------+
| Client | | users | | usercards |
+-----------+ +-----------+ +----------------+
|*ClientID |-+,--,--, |*IDUser |-+---, |*IdUserCard |
| name | | | | | name | '-----+<| IdUser |
+-----------+ | | | | CompanyId |>+-, | IdCard |>+---,
| | '-----+<| ClientID | | | userCardNumber | |
| | +-----------+ | +----------------+ |
| | | |
| | | |
| | +-----------+ | +----------------+ |
| | | company | | | company_cards | |
| | +-----------+ | +----------------+ |
| | |*CompanyID |-+-'--, |*IdcompanyCard | |
| | | name | '----+<| CompanyId | |
| '--------+<| ClientId | | IdCard |>+-, |
| +-----------+ +----------------+ | |
| | |
| | |
| | |
| +----------------+ +-----------+ | |
| | Client_cards | | Cards | | |
| +----------------+ +-----------+ | |
| |*IdclientCard | ,--+<|*IdCard |-+-----'-'
'-----------+<| ClientID | | | cardName |
| IdCard |>+--' | cardURL |
+----------------+ +-----------+
January 31, 2015 at 12:42 pm
Could you please check your data sample, somehow id doesn't look right or I'm missing something
😎
January 31, 2015 at 2:35 pm
Hi Eirik,
thanks for your reply. Yes, your diagram looks perfect for me. i am not missing anything here. any help please
February 1, 2015 at 5:46 pm
I see at least 2 issues here:
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;
If as you've said your data model is what Erikur suggests, and assuming you have an FK constraint from @usercards back into @users, the second insert above is going to fail because there is no idUser of 200 or 300.
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.]
The above does not run either, because you've got a literal numeric in the list of columns to insert (all 3 inserts).
Please correct your data and your SQL.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 2, 2015 at 6:07 am
Hi Dwain,
thanks for the reply and i copied data i posted and ran. it didn't throw any error. Also below is my another try to get the required output.
data:
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;
logic:
Declare @IdUser int = 100;
select c.*, coalesce(a.IsUserSpecific, 0) as IsUserSpecific
from @users as u
cross join @Cards as c
outer apply
(
select top 1 1 as IsUserSpecific from @usercards as uc
where uc.IDUser = u.IDUser and uc.IdCard = c.IdCard
) as a
where
u.IDUser = @IdUser and
(a.IsUserSpecific = 1 or
exists (select 1 from @Client_cards as cc
where cc.ClientID = u.ClientID and cc.IdCard = c.IdCard) or
exists (select 1 from @company_cards as cc
where cc.CompanyId = u.CompanyId and cc.IdCard = c.IdCard));
I didn't change anything i posted in my sample data.
full sql:
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;
Declare @IdUser int = 100;
select c.*, coalesce(a.IsUserSpecific, 0) as IsUserSpecific
from @users as u
cross join @Cards as c
outer apply
(
select top 1 1 as IsUserSpecific from @usercards as uc
where uc.IDUser = u.IDUser and uc.IdCard = c.IdCard
) as a
where
u.IDUser = @IdUser and
(a.IsUserSpecific = 1 or
exists (select 1 from @Client_cards as cc
where cc.ClientID = u.ClientID and cc.IdCard = c.IdCard) or
exists (select 1 from @company_cards as cc
where cc.CompanyId = u.CompanyId and cc.IdCard = c.IdCard));
Sorry, i didn't get any error. Any suggestions/corrections please
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply