February 28, 2007 at 3:24 pm
Guys,
I have tricky situation with alias_ids we use for some of the employee ids we use/store.
Person and Employee table as more than couple million records, below is the sample data.
Person Table
PersonidEmpid
_____________________
1101
2202
Employee Table
EmpidLnameAlias_flag
__________________________
1TomN
2TimN
3 Tom, akaY
4Tom, aka2Y
Alias Table
Alias_idEmpid
________________________
31
41
This is my query to join Person and Employee tables
select p.personid, p.empid, e.lname
from person p inner join employee e on
p.empid = e.empid
Result
Personidempidlname
_______________________________
1101Tom
2202Tim
What I am trying to do is since empid has aliases 3, 4 associated with it I want the result to be
Personidempidlname
_______________________________
1101Tom
2202Tim
1103Tom, aka
1104Tom, aka2
For this I do not want to use 'UNION' since this brings down the performance as I am dealing with 3 millions rows in each Person adn employee table with different conditions in the 'where' clause.
Is there any simple way to accomplish this without using 'UNION'?
any suggestions/inputs would help
Thanks
February 28, 2007 at 3:48 pm
Use UNION ALL so that the 2 resultsets aren't subjected to sort/distinct operations.
February 28, 2007 at 7:34 pm
Try this: (I craeted tables and populated data for testing in the beginning.)
SET NOCOUNT ON
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Alias]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Alias]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employee]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Person]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Person]
create Table dbo.Person (
Personid int not null,
Empid int not null
)
insert into dbo.Person
select 110,1 Union All
Select 220,2
create Table dbo.Employee (
Empid INT not null,
Lname Varchar(30) Null,
Alias_flag Varchar(1) Null,
Alias Varchar(30) Null
)
Insert Into dbo.Employee
Select 1, 'Tom', 'N',null Union All
Select 2, 'Tim', 'N',null Union All
Select 3, 'Tom', 'Y', 'aka' Union All
Select 4, 'Tom', 'Y', 'aka2'
create Table dbo.Alias (
Alias_id INT Not Null,
Empid INT Not Null)
Insert Into dbo.Alias
Select 3,1 Union all
Select 4,1
-- Now you have the result here
-- You need a sub-query for temp result then join it with person table. I hilighted some columns for your understading.
Select p.Personid, ss.eid, ss.Alias
From person p inner join
(select e.lname, isnull(a.empid,e.empid) aid, e.empid eid, e.lname + ' ' + isnull(e.Alias,'') Alias
from employee e
left join alias a on
e.empid=a.alias_id) as ss
On p.empid=ss.aid
March 1, 2007 at 3:27 am
Using Terry's code above, I *think* if you're examining entire tables it might help to avoid the subquery:
select p.personid, e.empid, e.lname + ' ' + coalesce(e.alias, '')
from employee e
left join alias a
on a.alias_id = e.empid
left join person p
on coalesce(a.empid, e.empid) = p.empid
Though I could have just made that up.
Jon
March 1, 2007 at 1:40 pm
Dear IT-75, what a charming code you have. Thanks for your most appropriate advice using coalesce function. I am learning a lot everyday. -Terry
March 1, 2007 at 2:04 pm
Terry, Thanks for the reply, I tried your query below doesnt seem to work below - there is a little change the tables
Please dont think this as a puzzle the only other thing I can do if the query doesnt work is added case_id to
alias table update the column and then write a query to display the result, but that seems to not work too.
SELECT e.person_alias_id, C.caseid
FROM
person e
LEFT OUTER JOIN
Alias a
ON e.person_alias_id = a.Alias_ID
LEFT OUTER JOIN
Party p
ON p.person_alias_id = e.person_alias_id
OR a.alias_id = p.person_alias_id
LEFT OUTER JOIN [fCASE] C
ON C.CASEID = P.CASE_ID
case table
case_id
___________
31571
party table
person_alias_idcase-id
_______________________________
3091131571
person table
person_alias_idlastname
_________________________________
30911Tom
30912Tom, aka
30200Tom, aka2
alias table
entity_idalias_id
________________________
3091130912
3091130200
Result
person_alias_id case_idlastname
_________________________________________
3091131571 Tom
3091231571Tom, aka
3020031571Tom, aka2
any suggestions/inputs to write this query
Thanks
March 1, 2007 at 3:19 pm
I modified the code as below:
SET NOCOUNT ON
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Alias]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Alias]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Case]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Case]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Person]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Person]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Party]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Party]
create Table dbo.[Case] (
Case_ID int not null
)
Insert Into dbo.[Case]
Select 31571
create Table dbo.Party (
Person_alias_ID int not null,
Case_ID int not null
)
insert into dbo.Party
Select 30911,31571
create Table dbo.Person (
Person_alias_ID INT not null,
LastName Varchar(30) Null,
--Alias_flag Varchar(1) Null,
Alias Varchar(30) Null
)
Insert Into dbo.Person
Select 30911, 'Tom',null Union All
Select 30912, 'Tom','aka' Union All
Select 30200, 'Tom','aka2'
create Table dbo.Alias (
Entity_ID INT Not Null,
Alias_id INT Not Null
)
Insert Into dbo.Alias
Select 30911,30912 Union all
Select 30911,30200
-- Now you have the result here
-- You need a sub-query for temp result then join it with Party table. I hilighted some columns for your understading.
Select c.Case_ID, ss.eid, ss.Alias
From Party p inner join
(select e.LastName, isnull(a.Entity_ID,e.Person_alias_ID) aid, e.Person_alias_ID eid, e.LastName + ' ' + isnull(e.Alias,'') Alias
from Person e
left join alias a on
e.Person_alias_ID=a.alias_id) as ss
On p.Person_alias_ID=ss.aid
Inner Join dbo.[Case] c
On p.Case_ID=c.Case_ID
-- I modified the script written by it-75 according to the change in tables but I think this is better.
select c.case_id , e.person_alias_id , e.LastName + ' ' + coalesce(e.alias, '')
from person e
left join alias a
on a.alias_id = e.person_alias_id
left join party p
on coalesce(a.entity_id, e.person_alias_id) = p.person_alias_id
Inner Join dbo.[Case] c
On p.Case_ID=c.Case_ID
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply