Help with joins - 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

General SQL Server Forums

New to SQL Server Programming

Help with joins

Author

Topic

SIRHACK
Starting Member

5 Posts

Posted - 2011-01-23 : 04:41:18

Hi,

I'm helping out a buddy and I'm new to SQL. I have questions on nested SELECT statements versus JOINs. Don't know if this is correct place to post this or should it go to Transact-SQL (2008)forum? The following is the issue.

I have two tables.

Foursomes$ table:
FoursomeID int
PairingID int
GroupNum int
StartingHole nchar(5)
[Player 1] nvarchar(30)
[Player 2] nvarchar(30)
[Player 3] nvarchar(30)
[Player 4] nvarchar(30)

And a Players Table:

PlayerID int
Name nvarchar(30)
[GHIN No] int
HI decimal(38,2)
emailaddr nvarchar(50)

I'm trying to write the query that would give me the following result set.

GroupNum HoleStart Time [Player_1] Player1email [Player 2] player2email [Player 3] Player3email [Player 4] player4email

I've tried self joins on the Players$ table, CROSS JOIN on Players and various other joins. Nothing produces the result that I want. Is there a simple way to get this result either using nested SELECTS or JOINs??

Any help would be appreciated.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-23 : 07:24:46

what have you tried? we can probably guide you to the correct syntax, but show has what you have done. Post some sample data and expected results so the problem is demonstrated.

Looks like you need to join on PLayerID which doesn't exist in the Foursome table, so you would have to join on the Players table 4 times


Select 
a.Groupnum,a.StartingHole, 
a.[Player 1],p1.Player1Email ,
a.[Player 2], p2.PlayerEmail
,a.[Player 3], p3.PlayerEmail,
a.[Player 4], p4.PlayerEmail
FROM [Foursome$] a join Players p1 on a.[Player 1] = p1.[name]
                    join Players p2 on a.[Player 2] = p2.[name]
                    join Players p3 on a.[Player 3] = p3.[name]
                    join Players p4 on a.[Player 4] = p4.[name]

Poor planning on your part does not constitute an emergency on my part.

SIRHACK
Starting Member

5 Posts

Posted - 2011-01-23 : 22:18:06

Thanks, this is much closer than I ever got before. Only one remaining issue to resolve. When one of the Player fields (i.e., [Player 1], [Player 2], [Player 3], or [Player 4] is NULL then I currently don't get that row within the results set. For example rows with GroupNum = to 2,3,5,9,10,12 &13 are not present.

I've included a sample Foursomes$ table along with the query and actual result set from running that query against the data. I have also included my expected results. In short the existing query returns 6 rows, which all have four players within. The seven rows which have one or more NULL players aren't included. Ultimate result set should include all 13 rows.

Thanks,

Actual Query

Select
a.FoursomeID,a.PairingID,a.Groupnum,a.StartingHole,
a.[Player 1],p1.emailaddr as p1email,
a.[Player 2], p2.emailaddr as p2email,
a.[Player 3], p3.emailaddr as p3email,
a.[Player 4], p4.emailaddr as p4email
FROM [Foursomes$] a join Players$ p1 on (a.[Player 1] = p1.[name] )
join Players$ p2 on (a.[Player 2] = p2.[name] )
join Players$ p3 on (a.[Player 3] = p3.[name] )
join Players$ p4 on (a.[Player 4] = p4.[name] )
where a.PairingID = 1
ORDER BY a.GroupNum

Partial Foursomes$ Table

FoursomeID PairingID GroupNo StartingHole Player 1 Player 2 Player 3 Player 4
1 1 1 NULL Charlie Baughman Jim McMenamin Carlos Ramirez Harold Williams
2 1 2 NULL NULL Dick Pasqua Mike Mason Abe Rivera
3 1 3 NULL Dennis Heintz NULL Harold Williams Robert Mccullough
4 1 4 NULL Billy Clift Dick Gaspari Len Sampaio Phil Braverman
5 1 5 NULL NULL NULL NULL Ken Sandow
6 1 6 NULL Art Mallo Gary Johnson Jan Olsen Tom Dennis
7 1 7 NULL Bob Pemberton Thomas Werth Kevin Smith Ken Northgrave
8 1 8 NULL Nikki Witt Richard Witt Bill Murell Ken VonDeylen
9 1 9 NULL Pat Ramirez Dorothy Howard NULL Milton L Gray
10 1 10 NULL Fred Cimperman Fred Lindauer NULL Frank Santos
11 1 11 NULL Harold Berman Gary Tamblin Patrick Sproul Paul S Cova
12 1 12 NULL Billy Stull Buzz Howard NULL Patrick Sproul
13 1 13 NULL Bob Widman NULL Ed Cruz Jack Hirshon
14 1 14 NULL NULL NULL NULL NULL
15 1 15 NULL NULL NULL NULL NULL
16 1 16 NULL NULL NULL NULL NULL
17 1 17 NULL NULL NULL NULL NULL
18 1 18 NULL NULL NULL NULL NULL
19 1 19 NULL NULL NULL NULL NULL
20 1 20 NULL NULL NULL NULL NULL
21 2 1 NULL Dean Blair Gordon Leighton Mervin Mack Larry Love
22 2 2 NULL NULL Tom Augustine Frank Santos Dan Tyler
23 2 3 NULL NULL Jim Kraus Mervin Mack John Griffin
24 2 4 NULL Don Phillips Rich Johnsen Tony Felice Bob Widman
25 2 5 NULL Fran Papineau NULL Robert Mancini Art Mallo

Actual query results

FoursomeID PairingID Groupnum StartingHole Player 1 p1email Player 2 p2email Player 3 p3email Player 4 p4email
1 1 1 NULL Charlie Baughman Charlie@Baughman.com Jim McMenamin Jim@McMenamin.com Carlos Ramirez Carlos@Ramirez.com Harold Williams Harold@Williams.com
4 1 4 NULL Billy Clift Billy@Clift.com Dick Gaspari Dick@Gaspari.com Len Sampaio Len@Sampaio.com Phil Braverman Phil@Braverman.com
6 1 6 NULL Art Mallo Art@Mallo.com Gary Johnson Gary@Johnson.com Jan Olsen Jan@Olsen.com Tom Dennis Tom@Dennis.com
7 1 7 NULL Bob Pemberton Bob@Pemberton.com Thomas Werth Thomas@Werth.com Kevin Smith Kevin@Smith.com Ken Northgrave Ken@Northgrave.com
8 1 8 NULL Nikki Witt Nikki@Witt.com Richard Witt Richard@Witt.com Bill Murell Bill@Murell.com Ken VonDeylen Ken@VonDeylen.com
11 1 11 NULL Harold Berman Harold@Berman.com Gary Tamblin Gary@Tamblin.com Patrick Sproul Patrick@Sproul.com PaulS Cova Paul@SCova.com

Expected Query Results:

FoursomeID PairingID Groupnum StartingHole Player 1 p1email Player 2 p2email Player 3 p3email Player 4 p4email

1 1 1 NULL Charlie Baughman Charlie@Baughman.com Jim McMenamin Jim@McMenamin.com Carlos Ramirez Carlos@Ramirez.com Harold Williams Harold@Williams.com
2 1 2 NULL NULL NULL Dick Pasqua Dick@Pasqua.com Mike Mason Mike@Mason.com Abe Rivera Abe@Rivera.com
3 1 3 NULL Dennis Heintz Dennis@Heintz.com NULL NULL Harold Williams Harold@Williams.com Robert Mccullough Robert@Mccullough.com
4 1 4 NULL Billy Clift Billy@Clift.com Dick Gaspari Dick@Gaspari.com Len Sampaio Len@Sampaio.com Phil Braverman Phil@Braverman.com
5 1 5 NULL NULL NULL NULL NULL NULL NULL Ken Sandow Ken@Sandow.com
6 1 6 NULL Art Mallo Art@Mallo.com GaryJohnson Gary@Johnson.com Jan Olsen Jan@Olsen.com Tom Dennis Tom@Dennis.com
7 1 7 NULL Bob Pemberton Bob@Pemberton.com Thomas Werth Thomas@Werth.com Kevin Smith Kevin@Smith.com Ken Northgrave Ken@Northgrave.com
8 1 8 NULL Nikki Witt Nikki@Witt.com Richard Witt Richard@Witt.com Bill Murell Bill@Murell.com Ken VonDeylen Ken@VonDeylen.com
9 1 9 NULL Pat Ramirez Pat@Ramirez.com Dorothy Howard Dorothy@Howard.com NULL NULL Milton L Gray Milton@Gray.com
10 1 10 NULL Fred Cimperman Fred@Cimperman.com Fred Lindauer Fred@Lindauer.com NULL NULL Frank Santos Frank@Santos.com
11 1 11 NULL Harold Berman Harold@Berman.com Gary Tamblin Gary@Tamblin.com Patrick Sproul Patrick@Sproul.com Paul S Cova Paul@SCova.com
12 1 12 NULL Billy Stull Billy@Stull.com Buzz Howard Buzz@Howard.com NULL NULL Patrick Sproul Patrick@Sproul.com
13 1 13 NULL Bob Widman Bob@Widman.com NULL NULL Ed Cruz Ed@Cruz.com Jack Hirshon Jack@Hirshon.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-23 : 22:57:35

change the query to use LEFT JOIN


Select 
a.Groupnum,a.StartingHole, 
a.[Player 1],p1.Player1Email ,
a.[Player 2], p2.PlayerEmail
,a.[Player 3], p3.PlayerEmail,
a.[Player 4], p4.PlayerEmail
FROM [Foursome$] a  LEFT join Players p1 on a.[Player 1] = p1.[name]
                    LEFT join Players p2 on a.[Player 2] = p2.[name]
                    LEFT join Players p3 on a.[Player 3] = p3.[name]
                    LEFT join Players p4 on a.[Player 4] = p4.[name]

KH
[spoiler]Time is always against us[/spoiler]

SIRHACK
Starting Member

5 Posts

Posted - 2011-01-24 : 01:38:52

Thanks so much. It works just the way I want it to.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-24 : 07:10:06

Delete the actual query results from your post above...no need to publicize actual names and email addresses now that you have the answer.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources