asked on

SQL Join with mulitple rows in 2 tables

I need an SQL query with multiple joins and I can't figure out the correct sytax.

TABLE 1
ID FNAME LNAME
1 John Doe
2 Mike Murphy
3 Rick Anderson

TABLE 2
GOAL ASSIST1 ASSIST2
2 1 NULL
1 NULL NULL
1 2 3

Basically I need to display the recordset from Table 2 as the following:

Mike Murphy, John Doe
John Doe
John Doe, Mike Murphy, Rick Anderson

Eyal

something like this

select (t11.FNAME + ' ' + t11.LNAME) as GOAL,
(t12.FNAME + ' ' + t12.LNAME) as ASSIST1,
(t13.FNAME + ' ' + t13.LNAME) as ASSIST2
From  TABLE2 t2 outer join 
TABLE1 t11 on t2.Goal = t11.ID  outer join 
TABLE1 t12 on t2.Assist1 = t12.ID  outer join 
TABLE1 t13 on t2.Assist2 = t13.ID

Open in new window

sportzone

ASKER

That sytax sounds right and makes sense to me but it threw this error: "Incorrect syntax near the keyword 'join'."

Eyal

you are right, here is my working test

CREATE TABLE dbo.Table1
	(
	ID int NOT NULL,
	FNAME nchar(10) NULL,
	LNAME nchar(10) NULL
	)  ON [PRIMARY]
GO

CREATE TABLE dbo.Table2
	(
	GOAL int NOT NULL,
	ASSIST1 int NULL,
	ASSIST2 int NULL
	)  ON [PRIMARY]
GO

INSERT INTO TABLE1 values(1,'John','Doe')
INSERT INTO TABLE1 values(2,'Mike','Murphy')
INSERT INTO TABLE1 values(3,'Rick','Anderson')

INSERT INTO TABLE2 values (2,1,NULL)
INSERT INTO TABLE2 values (1,NULL,NULL)
INSERT INTO TABLE2 values (1,2,3)

select (t11.FNAME + ' ' + t11.LNAME) as GOAL,
(t12.FNAME + ' ' + t12.LNAME) as ASSIST1,
(t13.FNAME + ' ' + t13.LNAME) as ASSIST2
From  TABLE2 t2 left join 
TABLE1 t11 on t2.Goal = t11.ID  left join 
TABLE1 t12 on t2.Assist1 = t12.ID  left join 
TABLE1 t13 on t2.Assist2 = t13.ID

Open in new window

sportzone

ASKER

I think we are really close. I have a bunch of additional colums in my tables that I didn't include in my example. The above example was to try to get a syntax into my code but it's returning 2700 rows when it should return 9 rows.

However, it is returning the proper names from the Players table (Table 1)

I am using VB script and here is how I have it.

I appologize, I am very new to this...

strSQL = "select (t11.FNAME) as GOAL,(t12.FNAME) as ASSIST1,(t13.FNAME) as ASSIST2 From Game_Summary t2 left join Players t11 on t2.Goal = t11.PlayerID left join Players t12 on t2.[1Assist] = t12.PlayerID left join Players t13 on t2.[2Assist] = t13.PlayerID where t2.GameID = " & intGameID & " ORDER BY Period ASC, convert(time(7), time, 114) ASC"
Set rsgoal = Server.CreateObject("ADODB.Recordset")
rsgoal.Open strSQL, objConnHockey, 0
do while rsgoal.eof = false
%>
<tr>
<td width="10"><div align="left"><%=counter%>.</div></td>
<td width="30"><div align="left"></div></td>
<td width="30"></td>
<td><div align="left"><%=rsgoal("goal") & " " & rsgoal("assist1") & " " & rsgoal("assist2")%></div></td>
</tr>
<%
counter = counter+1
rsgoal.movenext
loop
rsgoal.close
set rsgoal = nothing%>

ASKER CERTIFIED SOLUTION

Eyal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial