locked
Help with Selecet* statement RRS feed

  • Question

  • User-371148474 posted

    I have three tables,

    Table A is Products...its primary key is productID, inside a row, a product is uploaded by a User, taking a pUserID column as FK, to UserID in UserProfile

    Table B is UserProfile....its primary key is UserID, its a PK and references pUserID in Products.

    Table C is Review...its a table that with two FK, rUserID which references UserProfile.UserID and productID which references Products.productID

    Now when I run this select statement trying to get the reviews for a particular pUserID in products....it gives me this

    SELECT Review.*,UserProfile.uFName
    FROM Products
    LEFT OUTER JOIN Review ON Review.productID=Products.productID
    LEFT OUTER JOIN UserPRofile ON UserProfile.UserID=Products.pUserID
    WHERE (rReview IS NOT NULL )AND Products.pUserID=2
    ORDER BY uploadTime DESC

     rReviewID rUserID rRating                      rReview                               productID uFName

    1                  2             5     Hello, guys, welcome to my zanga                   10          John

    5                 2             3      Welcome to Wembatrix Specials                      5           John

    My problem is this, I want to convert the rUserID to a name from the UserProfile too....but Sql wont allow me join, giving alias already exist error...

    Help with that please

    Saturday, July 12, 2014 4:16 PM

Answers

  • User465171450 posted

    To get all the reviews for products, where the products are owned by a particular user would be:

    SELECT Review.*, reviewProfile.uFName as ReviewerName
    FROM Products
    INNER JOIN Review on Review.ProductId = Products.ProductID
    INNER JOIN UserProfile as reviewProfile on Review.rUserID = reviewProfile.UserId
    WHERE Products.pUserID = 2
    ORDER BY uploadTime DESC

    I changed the name because I think perhaps you may have also been trying to get the name of the person who owns the product, in which case you would receive the error you got because you can't have two uFName colums. You also would run into problems binding the UserProfile twice so I aliased it to make it understandable.

    You don't need left joins if each review is posted by someone. If you want all the reviews left that definitely doesn't need a left outer join, and if all of them have a profile attached you can use an inner join, otherwise switch to a left outer join when joining the review to the profile.

    If you also wanted to get the product owner's name you would do:

    SELECT Review.*, reviewProfile.uFName as ReviewerName, productProfile.uFName as ProductOwnerName
    FROM Products
    INNER JOIN UserProfile as productProfile on Products.pUserID = productProfile.UserId
    INNER JOIN Review on Review.ProductId = Products.ProductID
    INNER JOIN UserProfile as reviewProfile on Review.rUserID = reviewProfile.UserId
    WHERE Products.pUserID = 2
    ORDER BY uploadTime DESC

    Of course, that adds the product owners name to each record returned which is a bit of a waste and usually better done with two queries.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, July 13, 2014 1:48 PM

All replies

  • User465171450 posted

    If you are looking to get a user's name for a review you should be using the userid from the review to match to the userprofile and not the products if I understand your design correctly.

    SELECT Review.*,UserProfile.uFName
    FROM Products
    LEFT OUTER JOIN Review ON Review.productID=Products.productID
    Inner JOIN UserPRofile ON UserProfile.UserID=Review.rUserID
    WHERE (rReview IS NOT NULL ) AND Products.pUserID=2
    ORDER BY uploadTime DESC

    I switched to an inner join because if there is a userprofile for every review, then we don't need to worry about a left outer join.

    Alias already exists normally means that you are trying to choose a name in your select that is already present in your select. For example, Review.* will have a rUserID in it, and if you tried to change that by adding UserProfile.uFName as rUserID then you would receive this error because you can only have one rUserID. You may have to avoid using the Review.* and just select the Review table's fields that you want.

    Saturday, July 12, 2014 4:29 PM
  • User-371148474 posted

    What I am trying to achieve here is this, In the Products Table....all items there were posted by indivdual users....

    This users pick FK from UserProfile table..

    Now in the review Table, External Users who post/ didnt post to Product table but want to write reviews on items posted by users in Products table do that

    My thin is this, I want to get every reviews for a particular user in the Products table, while still joining the name of the User who left the review too....

    UserProfile Table      Products Table         Review Table

    UserID PK                 productID PK           productID FK

                                   pUserID FK              rUserID FK

    Thats the relationship

    Saturday, July 12, 2014 6:09 PM
  • User-371148474 posted

    Any Clues on how to achieve that......Still stranded

    Sunday, July 13, 2014 7:18 AM
  • User465171450 posted

    To get all the reviews for products, where the products are owned by a particular user would be:

    SELECT Review.*, reviewProfile.uFName as ReviewerName
    FROM Products
    INNER JOIN Review on Review.ProductId = Products.ProductID
    INNER JOIN UserProfile as reviewProfile on Review.rUserID = reviewProfile.UserId
    WHERE Products.pUserID = 2
    ORDER BY uploadTime DESC

    I changed the name because I think perhaps you may have also been trying to get the name of the person who owns the product, in which case you would receive the error you got because you can't have two uFName colums. You also would run into problems binding the UserProfile twice so I aliased it to make it understandable.

    You don't need left joins if each review is posted by someone. If you want all the reviews left that definitely doesn't need a left outer join, and if all of them have a profile attached you can use an inner join, otherwise switch to a left outer join when joining the review to the profile.

    If you also wanted to get the product owner's name you would do:

    SELECT Review.*, reviewProfile.uFName as ReviewerName, productProfile.uFName as ProductOwnerName
    FROM Products
    INNER JOIN UserProfile as productProfile on Products.pUserID = productProfile.UserId
    INNER JOIN Review on Review.ProductId = Products.ProductID
    INNER JOIN UserProfile as reviewProfile on Review.rUserID = reviewProfile.UserId
    WHERE Products.pUserID = 2
    ORDER BY uploadTime DESC

    Of course, that adds the product owners name to each record returned which is a bit of a waste and usually better done with two queries.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, July 13, 2014 1:48 PM