Help on SQL Query - 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

Other Forums

MS Access

Help on SQL Query

Author

Topic

skskal
Starting Member

1 Post

Posted - 2004-07-30 : 09:56:04

Here are the tables and this is what I want.

Table 1:Task Table 2:People
--------------- --------------
ID Name1 Name2 Task ID Name
1 1 2 A 1 John
2 3 3 B 2 Jim
3 2 4 C 3 Jab
4 2 2 A 4 Hab
5 4 1 F 5 Cab

Now I need to SQL query that can generate following:
Task Name1 Name2
A John Jim
B Jab Jab
C Jim Hab
A Jim Jim
F Hab John

Advnance thanks for your help.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-30 : 11:38:09

Use [_code][_/code] tags (remove the _ ) to format data the above is hard to figure out.

Table 1:Task                         Table 2:People              
---------------                      --------------
ID Name1 Name2 Task                  ID   Name
1  1      2     A                     1   John
2  3      3     B                     2   Jim
3  2      4     C                     3   Jab
4  2      2     A                     4   Hab
5  4      1     F                     5   Cab


Now I need to SQL query that can generate following:

Task   Name1    Name2
A      John     Jim
B      Jab      Jab
C      Jim      Hab
A      Jim      Jim
F      Hab      John

SELECT	DISTINCT a.Task, b.[Name] as Name1, c.[Name] as Name2
FROM	Task as a INNER JOIN People as b ON a.Name1 = b.[ID]
	INNER JOIN People as c ON a.Name2 = c.[ID]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources