Answered by:
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
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