Help with Selecet* statement

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

Answer 1

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.

Answer 2

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

Answer 3

User-371148474 posted

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

Answer 4

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.