May 29, 2008 at 1:03 am
I have two tables:
FactoryEmply Tab. contains FactID, EmplyID, PersonID etc.
StoreEmply Tab. contains StoreID, EmplyID, PersonID etc.
Realizing that a person (with unique PersonID) can work in several Factories and or Stores at the same time, I want to create a view that contains all the persons in the factories and stores so that I can find out who within a workplace (factory or store) also work at other workplace (factories or stores).
I use the following SQL statement:
SELECT FactID AS ID, EmplyID, PersonID, 'Factory' AS WorkPlace, FROM FactoryEmply
UNION ALL
SELECT StoreID AS ID, EmplyID, PersonID, 'Store' AS WorkPlace, FROM StoreEmply
ORDER BY ID, WorkPlace, PersonID
I did get all the enployees from all the factories plus all the enployees from all the stores. However, within the listing of employees in a workplace (factory or store), I cannot get the employees that works in other factories and/or stores, since they are listed under their respective workplace. What I want is to have such employees (who works in multiple workplaces) listed multiple times, each time under one of his/her workplace.
How can I accomplish this?
Appreciate any suggestion. Thanks.
sg2000
May 29, 2008 at 1:55 am
based on your query that's exactly what it should return.
SELECT FactID AS ID, EmplyID, PersonID, 'Factory' AS WorkPlace
FROM FactoryEmply
UNION ALL
SELECT StoreID AS ID, EmplyID, PersonID, 'Store' AS WorkPlace
FROM StoreEmply
ORDER BY ID, WorkPlace, PersonID
All factoryemply
and all storeemply.
The 'union all' accepts all rows from the individual statements (so no exclusions will be done overhere)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 29, 2008 at 10:58 am
ALZDBA, thanks for the response. However, the UNION as you and I used will not return multiple entries for the same person.
As I mentioned before, I have two tables:
FactoryEmply Tab. contains FactoryID, EmplyID, PersonID etc.
StoreEmply Tab. contains StoreID, EmplyID, PersonID etc.
As an example, I have a person with PersonID = 150 works only at Factory 21 (with Emply ID 119), and another person with PersonID = 110 works at Factory 21 (with Emply ID 199), Store 153 (with EmplyID 425) and Store 241 (with EmplyID 322) , and another person , I will have the following records in the tables:
FactoryEmply Tab.: two records: 21 | 119 | 150 and 21 | 199 | 110
StoreEmply Tab.: two records: 153 | 425 | 110 and 241| 322| 110
what I would like to get is:
ID | EmplyID | PersonID | WorkPlaceID | WorkPlace | WorkPlacEmpyID
21 |119 |150 |21 |Factory | 119
21 |199 |110 |21 |Factory | 199
21 |199 |110 |153 | Store |425
21 |199 |110 |241 | Store |322
153 |425|110 |21 | Factory | 199
153 |425|110 |153 |Store | 425
153 |425|110 |241 |Store | 322
241 |322 |110 |21 |Factory | 199
241 |322 |110 |153 | Store |425
241 |322 |110 |241 | Store |322
Here, each record is listed multiple times but from different perspectives (by FactoryID or StoreID). With this, when I retrieve the employees for a workplace (such as Factory 21), I can see who are working at multiple places.
Any ideas? (May be not with UNION ALL?)
Thanks,
sg2000
May 29, 2008 at 2:44 pm
I think you can get the result from your example by joining your original UNION query with itself, for example like this:
;WITH Empl(ID, EmplyID, PersonID, Workplace) AS
(
SELECT FactID AS ID, EmplyID, PersonID, 'Factory' AS WorkPlace, FROM FactoryEmply
UNION ALL
SELECT StoreID AS ID, EmplyID, PersonID, 'Store' AS WorkPlace, FROM StoreEmply
)
SELECT e1.ID, e1.EmplyID, e1.PersonID, e2.ID, e2.Workplace, e2.EmplID
FROM Empl e1 INNER JOIN Empl e2 ON e1.PersonID = e2.PersonID
ORDER BY e1.ID, e1.EmplyID, e2.WorkPlace, e2.EmplID
hth
May 29, 2008 at 9:44 pm
zdravko , what a great solution! Yes, it does work (though I use the DISTINCT phrase on the last Select to eliminate some duplications). Thanks very very much for you help and much appreciated.
sg2000
May 30, 2008 at 12:52 am
Indeed using the cte for this is a great solution.
There is a little caveot concerning data usage.
You absolutely need to know where you're comming from to be able to link the ID to a certain table. (id value may exist in both tables!)
So I just added e1.Workplace to the final select added
the columnalias SecondWorkplace.
set nocount on
Declare @FactoryEmply table (FactoryID int, EmplyID int, PersonID int )
insert into @FactoryEmply values(21 , 119 , 150 )
/* so person 110 works in factoryid 21 using multiple EmplyID ??? */
insert into @FactoryEmply values(21 , 199 , 110 )
insert into @FactoryEmply values(21 , 425 , 110 )
insert into @FactoryEmply values(21 , 322 , 110 )
Declare @StoreEmply table (StoreID int, EmplyID int, PersonID int )
insert into @StoreEmply values(153 , 425 , 110)
insert into @StoreEmply values(241 , 322 , 110)
/* person 112 works in two stores */
insert into @StoreEmply values(500 , 911 , 112)
insert into @StoreEmply values(501 , 922 , 112)
SET NOCOUNT OFF
;WITH cteEmpl(ID, EmplyID, PersonID, Workplace) AS
(
SELECT FactoryID AS ID, EmplyID, PersonID, 'Factory' AS WorkPlace
FROM @FactoryEmply
UNION ALL
SELECT StoreID AS ID, EmplyID, PersonID, 'Store' AS WorkPlace
FROM @StoreEmply
)
SELECT e1.ID, e1.Workplace, e1.EmplyID, e1.PersonID, e2.ID, e2.Workplace as OtherWorkPlace, e2.EmplyID
FROM cteEmpl e1
INNER JOIN cteEmpl e2
ON e1.PersonID = e2.PersonID
ORDER BY e1.ID, e1.EmplyID, e2.WorkPlace, e2.EmplyID
;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 30, 2008 at 1:40 am
ALZDBA, thank you for this valuable addition. You are absolutely right.
sg2000, I think you don't need DISTINCT at all. There should be no duplication if you take into consideration ALZDBA's remark.
May 30, 2008 at 10:14 pm
Thank you very much, ALZDBA, for the revised solution. Both of you are really great.
sg2000
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply