Query without using 'UNION' - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Query without using 'UNION'

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-02-28 : 17:24:19

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

Personid Empid
_____________________
110 1
220 2

Employee Table

Empid Lname Alias_flag
__________________________
1 Tom N
2 Tim N
3 Tom, aka Y
4 Tom, aka2 Y

Alias Table

Alias_id Empid
________________________
3 1
4 1

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

Personid empid lname
_______________________________
110 1 Tom
220 2 Tim

What I am trying to do is since empid has aliases 3, 4 associated with it I want the result to be

Personid empid lname
_______________________________
110 1 Tom
220 2 Tim
110 3 Tom, aka
110 4 Tom, 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

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-02-28 : 17:36:05

I'm not sure I understand - how are 3 and 4 related to 1, just by the flag being Y and the fact that the first part of the names are the same?

You should have an alias column in the table that holds 1 for 3 and 4. Then you could just join to that column too.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-02-28 : 18:05:54

I think this works as you described it:

-- Setup
DECLARE @Person TABLE(PersonID INT, EmpID INT)

INSERT @Person
SELECT 110, 1 UNION ALL
SELECT 220, 2

DECLARE @Employee TABLE(EmpID INT, LName VARCHAR(100), ALias_Flag CHAR(1))

INSERT @Employee 
SELECT 1, 'Tom', 'N' UNION ALL
SELECT 2, 'Tim', 'N' UNION ALL
SELECT 3, 'Tom, aka', 'Y' UNION ALL
SELECT 4, 'Tom, aka2', 'Y'

DECLARE @Alias TABLE(Alias_ID INT, EmpID INT)

INSERT @Alias
SELECT 3, 1 UNION ALL
SELECT 4, 1

-- Query
SELECT
	p.PersonID,
	e.EmpID,
	e.LName
FROM
	@Employee e
LEFT OUTER JOIN
	@Alias a
	ON e.EmpID = a.Alias_ID
LEFT OUTER JOIN
	@Person p
	ON p.EmpID = e.EmpID
	OR a.EmpID = p.EmpID

-Ryan

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-03-01 : 15:58:04

lamprey, Thank you for the reply, I tried your query below doesnt seem to work below - there is a little change the tables

Please don't think this as a puzzle the only other thing I can do if the query doesn't 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_id case-id
_______________________________
30911 31571

person table

person_alias_id lastname
_________________________________
30911 Tom
30912 Tom, aka
30200 Tom, aka2

alias table

entity_id alias_id
________________________
30911 30912
30911 30200

Result

person_alias_id case_id lastname
_________________________________________
30911 31571 Tom
30912 31571 Tom, aka
30200 31571 Tom, aka2

any suggestions/inputs to write this query

Thanks

rlaubert
Yak Posting Veteran

96 Posts

Posted - 2007-03-01 : 16:13:24

How about a left outer join and a having clause. Left outer join on person table so all records are returned and join on the other tables where there are matches. Having clause if you only want records with alais

Raymond Laubert
MCDBA, MCITP:Administration, MCT

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-03-01 : 17:23:18

can you please elaborate ?, I think thats what I doing in query

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-01 : 17:40:49

Hehe, I had a couple minutes before I meeting. I think this will work.

-- SET UP
DECLARE @Case TABLE(case_id INT)

INSERT @Case
SELECT 31571

DECLARE @Party TABLE (person_alias_id INT, case_id INT)

INSERT @Party
SELECT 30911, 31571

DECLARE @Person TABLE (person_alias_id INT, lastname VARCHAR(100))

INSERT @Person
SELECT 30911, 'Tom' UNION ALL
SELECT 30912, 'Tom, aka' UNION ALL
SELECT 30200, 'Tom, aka2' UNION ALL
SELECT 35000, 'Timmy'


DECLARE @Alias TABLE (entity_id INT, alias_id INT)

INSERT @Alias
SELECT 30911, 30912 UNION ALL
SELECT 30911, 30200

-- QUERY
SELECT DISTINCT
	p.person_alias_id,
	par.case_id,
	p.lastname
FROM
	@Person p
LEFT OUTER JOIN
	@Alias a
	ON p.person_alias_id = a.entity_id
	OR p.person_alias_id = a.alias_id
LEFT OUTER JOIN	
	@Party par
	ON a.entity_id = par.person_alias_id
LEFT OUTER JOIN
	@Case c
	ON par.case_id = c.case_id
WHERE
	c.case_id IS NOT NULL

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-01 : 17:43:21

Also, here is another one without the join to Case as it should not be needed.

SELECT DISTINCT
	p.person_alias_id,
	par.case_id,
	p.lastname,
	par.case_id
FROM
	@Person p
LEFT OUTER JOIN
	@Alias a
	ON p.person_alias_id = a.entity_id
	OR p.person_alias_id = a.alias_id
LEFT OUTER JOIN	
	@Party par
	ON a.entity_id = par.person_alias_id
WHERE
	par.case_id IS NOT NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources