Newbie who needs help on an inner join.

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 2008 Forums

Transact-SQL (2008)

Newbie who needs help on an inner join.

Author

Topic

NewToASP
Starting Member

7 Posts

Posted - 2012-01-16 : 22:28:01

Man, I am really starting to get frustrated here. I used the query designer in SQL Server and it sets up my joins but I just can't get the select statement right.

Here is a picture of my database.

I want to select from the Tables Fight, Fighter, Event, and Weight so that I can retrieve "Weight.class, Winner.name, Loser.name, and Event.Name" through the joins. Everything else is in the Fight table.

This is what I got from the editor but I can't get the select statement to bring anything back.


SELECT     
FROM         FIGHT 
             INNER JOIN
             FIGHTER ON FIGHT.Winner = FIGHTER.ID AND FIGHT.Loser = FIGHTER.ID 
             INNER JOIN
             EVENT ON FIGHT.EventID = EVENT.ID 
             INNER JOIN
             WEIGHT ON FIGHT.WeightClass = WEIGHT.ID AND FIGHTER.WeightClass = WEIGHT.ID

The funny thing is that I figured out how to do it in LINQ to Entities but I can't figure out how to enable sorting and flitering from the code behind so I want to do it in SQL instead.

Pleae help, I'm desparate!

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 22:38:04

you need to put some columns in select list

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NewToASP
Starting Member

7 Posts

Posted - 2012-01-17 : 00:13:23

quote:
Originally posted by visakh16

you need to put some columns in select list

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Well I know that. lol

The problem is I can't seem to get the sytax right so that I can select the Winner and Loser's names along the the name of the WeightClass and Event.

I tried doing:

SELECT FIGHT.WeightClass.Class, FIGHT.Winner.Name, FIGHT.Loser.Name
FIGHT.EventID.Name

How can I get at the Winner, Loser, and EventID names and the WeightClass Class?

Somehow I feel like it would have been easier just harcoding them all in every table instead going the relational route. lol

I dont get it because when I query this:


SELECT     FIGHT.Winner, FIGHT.Loser
FROM         FIGHT

I get back the ID's of the winner's and loser's but if I just query this:


SELECT     FIGHT.Winner, FIGHT.Loser
FROM         FIGHT INNER JOIN
                      FIGHTER ON FIGHT.Winner = FIGHTER.ID AND FIGHT.Loser = FIGHTER.ID INNER JOIN
                      WEIGHT ON FIGHT.WeightClass = WEIGHT.ID

I get NO RESULTS! It makes no sense to me.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-17 : 04:06:12

You're asking for a single row from the fighter table where the ID is that of the winner AND the loser. ie, you're looking for cases where one person both won and lost the fight.

FIGHT.Winner = FIGHTER.ID AND FIGHT.Loser = FIGHTER.ID

. AND means both conditions must be true for a row to be returned, not one or the other.

Since, in most cases, it's a different person that wins the fight than the one who loses it, you need the fighter table to appear twice in the from clause. Once joined to Fight.Winner and once joined to Fight.Loser.

Make sense now?

--
Gail Shaw
SQL Server MVP

NewToASP
Starting Member

7 Posts

Posted - 2012-01-17 : 13:36:07

quote:
Originally posted by GilaMonster

You're asking for a single row from the fighter table where the ID is that of the winner AND the loser. ie, you're looking for cases where one person both won and lost the fight.
FIGHT.Winner = FIGHTER.ID AND FIGHT.Loser = FIGHTER.ID
. AND means both conditions must be true for a row to be returned, not one or the other.

Since, in most cases, it's a different person that wins the fight than the one who loses it, you need the fighter table to appear twice in the from clause. Once joined to Fight.Winner and once joined to Fight.Loser.

Make sense now?

--
Gail Shaw
SQL Server MVP

Yes, it does thanks. So you must join a table as many times as the number of it's foreign keys to get at all of them.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-17 : 13:46:10

It would be very helpful to see the table DDL and sample Data and expected results

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

NickC
Yak Posting Veteran

68 Posts

Posted - 2012-01-19 : 08:48:16

Can't you Left join fighter to fight both tiems

so

Select *

From Fight f

LEfT JOIN fighter win
on win.ID= f.Winner

LEFT Join fighter loss
on loss.ID= f.Loser

LEFT JOIN Event event
on event.ID = f.EventID

LEFT JOIN WEight winweight
on Win.ID = winweight.ID

LEFT JOIn Weight lossweight
on Loss.ID = lossweight.ID

NickC
Yak Posting Veteran

68 Posts

Posted - 2012-01-19 : 08:49:14

I'm useless btw at visuallising what people want without having the table in front of me, so apologies if wrong, I am a newbie too!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-19 : 09:07:36

Hi, NewToASP

Thanks for providing the information. What you are trying to do is something like this


SELECT
	e.[Name] AS [EventName],
	f.[ID] AS [FightID],
	fw.[Name] AS [WinnerName],
	fl.[Name] AS [LoserName],
	w.[Class] AS [WeightClass]
FROM
	-- Event details (event, fight, weight)
	EVENT AS e
	JOIN FIGHT AS ft ON ft.[EventID] = e.[ID]
	JOIN WEIGHT AS w ON w.[ID] = ft.[WeightClass]
	
	-- Get the Winner of the fight
	JOIN FIGHTER AS fw ON fw.[ID] = ft.[Winner]
	
	-- Get the Loser of the fight
	JOIN FIGHTER AS fl ON fl.[ID] = ft.[Loser]

SO lets go through the code

1) I use aliases for the tables. There are *very* useful, without them you will get lost. Example, when I join to the FIGHTER table to get the winner I name the table fw (shorthand for FIGHT-WINNER). I can then refer to any of the rows in that table matched by the query via that alias (you can see this in the SELECT portion of the query).

2) JOIN is shorthand for INNER JOIN (they mean the same thing, there is no harm writing INNER JOIN if it makes you happy)

3) My FROM segment:
I start with event because that's what you are really interested in (probably)
then join it to FIGHT. If there are multiple fights per event then you'll get one entry for each one.
then join FIGHT to WEIGHT assuming there is only one weight class per FIGHT.
then I want to get the winner. I make a join to FIGHTER on the FIGHT.WINNER link (but using the alias)
then I want to get the loser, I make a join to FIGHTER on the FIGHT.LOSER link (but using the alias)

Feel free to ask any questions -- think this should be pretty self explanatory though.

EDIT: -- don't worry about the square braces round things, that's just my style. You'd use square braces to escape column names or table names that have spaces or other characters in (or that are reserved sql keywords) I fell into the habbit of using them everywhere because I felt it looked ugly when some columns were braced and some weren't I haven't met anyone else that does that so it's just me being particularly picky -- I think it makes my code more readable though.
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2012-01-19 : 09:40:12

quote:
Originally posted by Transact Charlie

Hi, NewToASP
...
Charlie

Very nice post, Charlie.

FischMan

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-19 : 12:14:27

quote:
Originally posted by FischMan2
Very nice post, Charlie.
FischMan

Thanks!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources