Post reply

Select and outer join

  • June 17, 2005 at 4:53 am

    #76344

    I an a bit of a newbe with sql and am having a lot of difficulty getting the following to work. Basically I'm modifying an existing SP to add a LEFT OUTER JOIN to return me the number of comments in another table.

    SELECT     tp.photo_id, LEFT(tp.photo_name, 12) AS photo_name, tp.thumb_path, ta.creator_id, tp.create_date, DERIVEDTBL.NumberOfComments AS comments

    FROM         tbl_photos tp INNER JOIN

                 tbl_albums_photos tap ON tp.photo_id = tap.photo_id INNER JOIN

                 tbl_albums ta ON tap.album_id = ta.album_id LEFT OUTER JOIN

              (SELECT     COUNT(*) AS NumberOfComments

               FROM tbl_photo_comments tpc) derivedtbl ON tbl_photo_cumments.photo_id = tpc.photo_id

    WHERE     (tap.album_id = @album_id)

    ORDER BY tp.create_date DESC

    This keeps returning the error -

    The column prefix 'tbl_photo_cumments' does not match with a table name or alias name used in the query.

    Any help would be great.

    thanks

  • June 17, 2005 at 5:28 am

    #566367

    Change your ON Statement to derivedtbl instead of tbl_photo_comments.  Also, I don't think once you do that that your code will work.  You are attempting to join on Photo_id but are not including it in your "derivetbl"  That SELECT (I think) should be something like (SELECT Photo_id, COUNT(*) FROM dbo.tbl_photo_comments GROUP BY Photo_id)



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply