October 13, 2004 at 6:12 pm
I'm not sure this can be achieved, but I am so close that I think it must be possible.
I have two tables. One has a list of employees and an id number that references the id of the other table. There can be more that one employee referenced by the id number in the other table.
ie.
table dbo.csiNewSaleEvalSP
-------
id int,spid int
table dbo.custReferal
--------------------
evalid int, refname string
What I would like is to have out put that has the evaluation id and columns for each of the employee numbers that are associated with the evaluation
Currently I have something like...
SELECT Distinct dbo.custReferal.evalid, dbo.custReferal.refname, B.spid AS sp1, C.spid AS sp2
FROM dbo.csiNewSaleEvalSP C RIGHT OUTER JOIN
dbo.csiNewSaleEvalSP B RIGHT OUTER JOIN
dbo.custReferal ON B.evalid = dbo.custReferal.evalid ON C.evalid = dbo.custReferal.evalid AND C.id > B.id
ORDER BY dbo.custReferal.evalid
The problem is although this is very close the Right Outer Join creates rows for each combination of employees associated with the referal.
Thanks in advance,
Simon
October 14, 2004 at 7:08 am
The problem is you have to be able to set the order of the person and eliminate all but one in the join. If this is for a reporting application you might actually find it much easier to do in the application side than this. Otherwise you have to add a column to seperate each person uniquely so you can narrow each join to a specific person. Might could even generate in a view like you want.
October 15, 2004 at 1:27 am
Hi Simon,
some sample data and desired output result would help me and others to better understand the problem.
Regards,
Goce Smilevski.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply