January 13, 2014 at 12:19 pm
Hi,
my table detail:
Table1 : address
column(idClient int, idCompany int, address nvarchar(200))
Table2 : Emp
column(idEmployee,idClient int, idCompany int)
idEmployee - will be the parameter to the procedure or query from c#
i want to get the address based on the below condition,
if idcompany exists for idemployee in emp table then map the data to address table and get the address. else get the idclient from emp table and map it with address table to get the address
my quers is:
declare @IdCompany int,@IdClient int, @IdMEmployee int
select @IdCompany = IdTCompany,@IdClient = IdClientfrom emp where IdMEmployee = @IdMEmployee;
select address from Address where
(idcompany = @IdCompany or IdClient= @IdClient)
seems it doesn't producing the proper result. Is there any way to rephrase this sql
January 13, 2014 at 12:27 pm
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 13, 2014 at 12:40 pm
Here is the table structure with sample data,
Table : Address
select 1 as idAddress, 100 as idCompany, 1000 as IdClient, 'Address1'
union all
select 1 as idAddress, 101 as idCompany, 1000 as IdClient, 'Address2'
union all
select 1 as idAddress, null as idCompany, 1000 as IdClient, 'Address3'
Table : Emp
select 1 as IdEmployee, 100 as idCompany, 1000 as IdClient
union all
select 2 as IdEmployee, 101 as idCompany, 1000 as IdClient
union all
select 3 as IdEmployee, 103 as idCompany, 1000 as IdClient
if i give IdEmployee 1 then output should be 'Address1'
if i give IdEmployee 3 then output should be 'Address3'
Any suggestions
January 13, 2014 at 12:44 pm
SELECT
/* @IdCompany = */ e.IdTCompany,
/* @IdClient = */ e.IdClient,
/* @address = */ COALESCE(a1.address, a2.address)
FROM emp e
LEFT OUTER JOIN address a1 ON
a.idcompany = e.IdCompany
LEFT OUTER JOIN address a2 ON
a2.idclient = e.IdClient
WHERE IdMEmployee = @IdMEmployee;
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 13, 2014 at 12:50 pm
Not big on making this easy for anybody I see. Please post this as consumable data. Here is an example:
create table #Address
(
idAddress int,
idCompany int,
IdClient int,
MyAddress varchar(20)
)
insert #Address
select 1 as idAddress, 100 as idCompany, 1000 as IdClient, 'Address1'
union all
select 1 as idAddress, 101 as idCompany, 1000 as IdClient, 'Address2'
union all
select 1 as idAddress, null as idCompany, 1000 as IdClient, 'Address3'
create table #Emp
(
IdEmployee int,
IdCompany int,
IdClient int
)
insert #Emp
select 1 as IdEmployee, 100 as idCompany, 1000 as IdClient
union all
select 2 as IdEmployee, 101 as idCompany, 1000 as IdClient
union all
select 3 as IdEmployee, 103 as idCompany, 1000 as IdClient
select * from #Address
select * from #Emp
drop table #Address
drop table #Emp
Can you explain how these tables are related? Why and how would we get 'Address3' from IdEmployee = 3? There is nothing in the data to relate those two rows. It seems like your relation is based on idCompany but there isn't a row with 103.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 13, 2014 at 1:03 pm
Hi Sean,
Thanks for your time on this and a small correction made on the sample data
create table #Address
(
idAddress int,
idCompany int,
IdClient int,
MyAddress varchar(20)
)
insert #Address
select 1 as idAddress, 100 as idCompany, 1000 as IdClient, 'Address1'
union all
select 1 as idAddress, 101 as idCompany, 1000 as IdClient, 'Address2'
union all
select 1 as idAddress, null as idCompany, 1001 as IdClient, 'Address3'
create table #Emp
(
IdEmployee int,
IdCompany int,
IdClient int
)
insert #Emp
select 1 as IdEmployee, 100 as idCompany, 1000 as IdClient
union all
select 2 as IdEmployee, 101 as idCompany, 1000 as IdClient
union all
select 3 as IdEmployee, 103 as idCompany, 1001 as IdClient
select * from #Address
select * from #Emp
drop table #Address
drop table #Emp
Logic :
Get the IdCompany, IdClient from Emp based on IdEmployee
if the idCompany exists in the Address Table then get the Address
if the idcomapny doesn't exists in Address table then get the Address based on IdClient
I can write this using if else but checking is there any other method to achieve this.
January 13, 2014 at 1:11 pm
Did you look at the code I wrote??
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 13, 2014 at 1:25 pm
Yes, I tried your query and it gives wrong result.
pass the idEmployee as 2
It should give the result as 'Address2' only only time. But your query produces the two times.
The logic will be if you give IdEmployee as 2 then it will get 101 as IdCompany and 1000 as IdClient.
AS per my logic it should map the 101 with address table and get the address. On your query since you used left outer join it produces the results multiple time.
January 13, 2014 at 1:45 pm
Yeah, sorry, in too big a hurry, left something out of the code:
SELECT
/* @IdCompany = */ e.IdTCompany,
/* @IdClient = */ e.IdClient,
/* @address = */ COALESCE(a1.address, a2.address)
FROM emp e
LEFT OUTER JOIN address a1 ON
a1.idcompany = e.IdCompany
LEFT OUTER JOIN address a2 ON
a1.idcompany IS NULL AND
a2.idclient = e.IdClient
WHERE IdMEmployee = @IdMEmployee;
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 13, 2014 at 1:49 pm
That's an ugly db design.
Here's a possible solution.
DECLARE @idEmployee int = 3
SELECT TOP 1 MyAddress
FROM(
select 1 Priority, MyAddress
from #Address a
WHERE EXISTS( SELECT 1 FROM #Emp e
WHERE a.idCompany = e.IdCompany
AND e.IdEmployee = @idEmployee)
UNION ALL
select 2 Priority, MyAddress
from #Address a
WHERE EXISTS( SELECT 1 FROM #Emp e
WHERE a.IdClient = e.IdClient
AND e.IdEmployee = @idEmployee)
)x
ORDER BY Priority
January 13, 2014 at 1:55 pm
Hi Scott,
You solution works fine now and thanks a lot.
Hi Luis,
Thanks for your reply too and as i said just thought of doing it in single query other then using if else or exists clause.
thank you so much everyone's contribution on this post.
January 13, 2014 at 2:07 pm
Technically, it's a single query and there's no IF..ELSE. 😀
January 13, 2014 at 2:27 pm
Thank you Gentle Man!!:-)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply