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)
 Return combined values

Author  Topic 

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-07-16 : 01:35:40
Hi all,
I have three tables with this structure

Mst_Employee
1.Empid-int
2.EmpName-Varchar(48)

Sample entries

Empid EmpName
1 Dana
2 sekar
3 Karti

Mst_Report
1.Processid-int
2.Processdoneby-int
3.Resetby-int

Sample Entry

Mst_Report(sample entry)
Processid Processdoneby Resetby
1 2 1
2 1 2
3 3 1
Mst_Process
1.Processid-int
2.ProcessName-varchar(50)
Sample Entries
Processid ProcessName
1 Printing
2 Scanning


Now the Mst_reports will contain the all the transaction entries. I want to retrive the values
as follows.How can I ?

Processid ProcessName ProcessDoneby ProcessResetby
1 Printing Sekar Dana
2 Scanning Dana Sekar



Thanks in Advance
Dana



danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-07-16 : 03:09:08
Dear Peter/Khan
Or What method I can use here

Dana
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-16 : 03:34:40
You should know this after 172 posts!
-- Create sample data
DECLARE @Mst_Employee TABLE (Empid int, EmpName Varchar(48))

INSERT @Mst_Employee (Empid, EmpName)
SELECT 1, 'Dana' UNION ALL
SELECT 2, 'sekar' UNION ALL
SELECT 3, 'Karti'

DECLARE @Mst_Report TABLE (Processid int, Processdoneby int, Resetby int)

INSERT @Mst_Report (Processid, Processdoneby, Resetby)
SELECT 1, 2, 1 UNION ALL
SELECT 2, 1, 2 UNION ALL
SELECT 3, 3, 1

DECLARE @Mst_Process TABLE (Processid int, ProcessName varchar(50))

INSERT @Mst_Process (Processid, ProcessName)
SELECT 1, 'Printing' UNION ALL
SELECT 2, 'Scanning'

-- Do the simple work
SELECT r.ProcessID,
p.ProcessName,
pe.EmpName AS ProcessDoneBy,
re.EmpName AS ProcessResetBy
FROM @Mst_Report AS r
INNER JOIN @Mst_Process AS p ON p.ProcessID = r.ProcessID
INNER JOIN @Mst_Employee AS pe ON pe.EmpID = r.ProcessDoneBy
INNER JOIN @Mst_Employee AS re ON re.EmpID = r.ResetBy

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-07-16 : 05:23:11
Thanks Peter,
I tried with only one copy of mst_Process only. Now I learn that how to use one table
for more than one join Condition. Thanks a LOoooooooooooooooooot.

Danasekarane.A
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-16 : 05:25:02
It's called Table Alias.

You're welcome.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

- Advertisement -