JOIN help - 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 2005 Forums

Transact-SQL (2005)

JOIN help

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-01-16 : 04:38:04

I have two tables:

audit_trail
---------

item olduser newuser modifiedby

1011 56 20 20
0167 44 49 90
2025 12 20 20

and

users
------
name code

Billy 56
Sue 44
Jon 12
Joe 49
Eve 90
Mike 20

How can I query a selection which gives the following output please?

item olduser newuser modifiedby

1011 Billy Mike Mike
0167 Sue Joe Eve
2025 Jon Mike Mike

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-16 : 04:42:18

[code]select a.item,a.olduser,a.newuser,a.modifiedby,b.name from audit_trail a left join users b on modifiedby=code[/code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-16 : 04:50:47

select item, (select name from users where code = a.olduser) olduser,
(select name from users where code = a.newuser) newuser,
(select name from users where code = a.modifiedby) modifiedby
from audit_trail a

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-01-16 : 05:02:40

Many thanks bklr - much appreciated. I learned something new

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-16 : 05:03:25

ur welcome

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-01-16 : 05:08:57

Use this

declare @AuditTrail table(item int, olduser int, newuser int, modifiedby int)
insert into @AuditTrail
SELECT 1011, 56, 20, 20
UNION ALL
SELECT 0167, 44, 49, 90
UNION ALL
SELECT 2025, 12, 20, 20

DECLARE @users TABLE (name varchar(10), code int)
INSERT INTO @users
SELECT 'Billy', 56
UNION ALL
SELECT 'Sue', 44
UNION ALL
SELECT 'Jon', 12
UNION ALL
SELECT 'Joe', 49
UNION ALL
SELECT 'Eve', 90
UNION ALL
SELECT 'Mike', 20

SELECT AuditTrail.item, OldUser.name ,NewUser.name, ModifyBy.name
FROM @AuditTrail AuditTrail
LEFT JOIN @users OldUser on AuditTrail.olduser = OldUser.code
LEFT JOIN @users NewUser on AuditTrail.NewUser = NewUser.code
LEFT JOIN @users ModifyBy on AuditTrail.modifiedby = ModifyBy.code

Rahul Shinde

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-01-16 : 05:11:03

quote:
Originally posted by bklr

select item, (select name from users where code = a.olduser) olduser,
(select name from users where code = a.newuser) newuser,
(select name from users where code = a.modifiedby) modifiedby
from audit_trail a

If volume of data is more then is it a good solution?

Rahul Shinde

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-16 : 06:08:31

May BE Like This......

Create table Audit_Trail(item int, olduser int, newuser int, modifiedby int)
insert into Audit_Trail
SELECT 1011, 56, 20, 20 UNION ALL
SELECT 0167, 44, 49, 90 UNION ALL
SELECT 2025, 12, 20, 20

Create table users(name varchar(10), code int)
INSERT INTO users
SELECT 'Billy', 56 UNION ALL
SELECT 'Sue', 44 UNION ALL
SELECT 'Jon', 12 UNION ALL
SELECT 'Joe', 49 UNION ALL
SELECT 'Eve', 90 UNION ALL
SELECT 'Mike', 20

select a.item,b.name as modifiedby,c.name as newuser,d.name as olduser from Audit_Trail A
left join users B on A.modifiedby=B.code
left join users C on A.newuser=C.code
left join users D on A.olduser=D.code

Thanks,.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources