Will not join on all items in table, only first one

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

Transact-SQL (2000)

Will not join on all items in table, only first one

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-07-06 : 07:47:29

Phillip writes "When I run this it only returns one record which is the first joined record of the friends table. It doesnt step through the rest of the To_ID in the friends table.

SELECT * (((UF2_Comments INNER JOIN UF2_rt_Friends AS Friends ON (Friends.To_id=UF2_Comments.User_id))
INNER JOIN UF2_rt_users AS Photographer ON (Photographer.user_id=UF2_Comments.Photog_id))
INNER JOIN UF2_rt_users AS Critiquer on (Critiquer.user_id=UF2_Comments.User_id))
WHERE (dbo.UF2_Comments.Image_Active = 1) AND (dbo.UF2_Comments.Comment_Active = 1) AND (Friends.From_ID = 82) AND (UF2_Comments.Photog_ID = 82)

These are other derivations that I had tried.

SELECT * FROM UF2_rt_Friends AS f, UF2_Comments AS c, UF2_rt_images AS i
WHERE f.From_ID = '82' AND f.To_ID = c.User_id AND c.Image_ID = i.image_id AND i.user_id = f.From_Id

SELECT * FROM UF2_rt_Friends AS f, UF2_Comments AS c, UF2_rt_images AS i
WHERE f.To_ID = c.User_id AND c.Image_ID = i.image_id AND i.user_id = '82'

SELECT *
FROM (SELECT * FROM UF2_rt_Friends AS f, UF2_Comments AS c WHERE f.To_ID = c.User_ID AND f.From_ID = '82') AS d,
UF2_rt_images AS i
WHERE i.Image_ID = d.Image_ID AND i.user_id = '82'

There are 6 entries in the friends.to_id that are associated with 82 in the Friends.To_ID table.

Do I need some sort of subselect using an IN predicate on the friends table? if so I have no idea how to do it.

THanks,

Phil"

nr
SQLTeam MVY

12543 Posts

Posted - 2005-07-06 : 12:04:42

It's only returning one row because your filter is excluding the other rows.

build up the query and see which join is losing rows

SELECT * FROM UF2_rt_Friends AS f WHERE f.From_ID = '82'
that should give your 6 rows

SELECT * FROM UF2_rt_Friends AS f join UF2_Comments AS c on f.To_ID = c.User_id WHERE f.From_ID = '82'
if that only gives 1 then you know there is only one corresponding row in the UF2_Comments table.

You could also change all your joins to left outer joins (put all the entries in the where clause into the joins) and see which tables return nulls.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources