Multiples references

Transact-SQL
1

In table cfg200 there are 4 fields erep, prep, srep, and rrep which all contain employee id's. In table com001 are the fields emno(id) and nama (name). I'm trying to write a query where I don't have to join com001 4 times to cfg200 to get the employee name for the 4 different fields. Is there an uncomplicated way to do this?

Thanks,
Cardgunner

2

No. There are a couple of ways, but they are not "uncomplicated".

3

What does your final output look like? The following just spits out a list of emnos and corresponding names if those emnos are present in one of the four columns.

;WITH cte AS
(
	SELECT
		*
	FROM
		(SELECT [erep],[prep],[srep],[rrep] FROM cfg200 ) s
	UNPIVOT
	(	emno FOR col IN ([erep],[prep],[srep],[rrep]) )U
)
SELECT
	*
FROM
	cte c
	INNER JOIN com001  t ON t.erep = c.emno
4

What I am having to do now is

Select
cfg200.erep,
e.nama,
cfg200.prep,
p.nama,
cfg200.srep,
s.nama,
cfg200.rrep,
r.nama

from cfg200
join com001 as e on e.emno=cfg200.erep
join com001 as p on p.emno=cfg200.prep
join com100 as s on s.emno=cfg200.srep
join com100 as r on r.emno=cfg200.rrep

reseults

1023| harry | 2025 | steve | 3054 | moe| 4087 | larry

Is there a better way of doing this then 4 joins. It's not complicated but it might not be the easiest way.