Multiple inner 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

SQL Server 2005 Forums

Transact-SQL (2005)

Multiple inner joins

Author

Topic

pithhelmet
Posting Yak Master

183 Posts

Posted - 2008-10-13 : 10:57:24

Hi everyone -

I cannot believe i am having this much trouble with these joins...

I have a master table that references several sub tables,
and I need to join the sub-tables based on the ID in the master table...

SELECT
u.username as [User_Name],
f.fullname as [NFL_Favorite],
h.fullname as [NHL_Favorite]
FROM ##UserSelection as U
inner join [prod_central].[football].[dbo].[team] as f
inner join [prod_central].[hockey].[dbo].[team] as h
on
u.nfl_favorite = f.teamid and
u.nhl_favorite = h.teamid
WHERE [username] IS NOT NULL AND len([username])>1

How can i screw this up???

i get an syntax error on WHERE....

UGH, can someone show me the light?

thanks
tony

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 10:59:36

[code]SELECT u.username as [User_Name],
f.fullname as [NFL_Favorite],
h.fullname as [NHL_Favorite]
FROM ##UserSelection as U
inner join [prod_central].[football].[dbo].[team] as f on u.nfl_favorite = f.teamid
inner join [prod_central].[hockey].[dbo].[team] as h on u.nhl_favorite = h.teamid
WHERE len([username]) > 1[/code]

E 12°55'05.63"
N 56°04'39.26"

pithhelmet
Posting Yak Master

183 Posts

Posted - 2008-10-13 : 11:04:14

Excellent -

Here is what I finished with

SELECT
u.username as [User_Name],
f.fullname as [NFL_Favorite],
h.fullname as [NHL_Favorite]
FROM ##UserSelection as U,
[prod_central].[football].[dbo].[team] as f,
[prod_central].[hockey].[dbo].[team] as h
WHERE
u.nfl_favorite = f.teamid and
u.nhl_favorite = h.teamid
and
[username] IS NOT NULL AND len([username])>1

Thanks for the reply!!

take care
tony

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 11:08:45

Why

1) Use CROSS JOIN and then filter?
2) Both have IS NOT NULL and LEN() > 1?

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 11:12:15

Please note that better to use ANSI type join syntax as Peso suggested as this will not supported in future versions of sql server.

pithhelmet
Posting Yak Master

183 Posts

Posted - 2008-10-13 : 16:03:25

Hi everyone -

The question, 2) Both have IS NOT NULL and LEN() > 1?
is because the DAL would allow a SPACE to be used as a valid username... since creation it has been corrected, but users have created records with the space as the user name.

thanks for the reply

take care
tony

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 00:17:56

Try this