Query Question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
turbo2ltr
Forum Commoner
Posts: 29
Joined: Sun Jul 18, 2004 4:08 pm

Query Question

Post by turbo2ltr »

I have three tables.
Ratings, Users, and reviews.
The Ratings table has a users FK. The Reviews table has a rating FK. Not all ratings have a review.

Ratings
ratingid
rating_hid
rating_uid

users
userid
user_name

Reviews
reviewid
review_ratingid


I want to get all the ratings where "hid" =1. I also want to get the user info from users for that rating.

Code: Select all

SELECT  * 
FROM rating, users
WHERE rating.rating_hid =1 AND rating.rating_uid = users.userid
The above code works fine for that.

But now I want to get the review that is being pointed to in Ratings, if there is one., but can't figure it out.

Code: Select all

SELECT  * 
FROM rating, users, reviews
WHERE rating.rating_hid =1 AND rating.rating_uid = users.userid AND reviews.review_ratingid = rating.ratingid
This will only return rows if they have a review. So can do what I want in a single query or should I just do another query to get the review after I checked if there is one?

Thanks,
Mike
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

SELECT * FROM `rating` a
LEFT JOIN `users` b ON a.`rating_hid` = 1 AND a.`rating_uid` = b.`userid`
LEFT JOIN `reviews` c ON a.`ratingid` = c.`review_ratingid`
:?:
Post Reply