MySQL Forums
Forum List  »  Newbie

Re: Using 4 tables, need a search on all of them, please help :-)
Posted by: Shantanu Oak
Date: April 11, 2005 04:13AM

You need to left join the tables to the main reviews table.


CREATE TABLE `reviewers` (`ID` INT PRIMARY KEY, `Name` VARCHAR(99), `more_info` VARCHAR(99));

CREATE TABLE `artists` (`ID` INT PRIMARY KEY, Artist_Name VARCHAR(99), more_info VARCHAR(99));

CREATE TABLE `labels` (`ID` INT PRIMARY KEY, Record_label_Name VARCHAR(99), more_info VARCHAR(99));

CREATE TABLE `reviews` (`ID` INT PRIMARY KEY, artist_ID INT, reviewer_id INT,
band_id INT, more_info VARCHAR(99));

SELECT reviews.ID, reviews.artist_ID, reviews.reviewer_id, reviews.band_id,
artists.Artist_Name, reviewers.Name, labels.Record_label_Name
FROM reviews LEFT JOIN artists
ON reviews.artist_ID = artists.ID
LEFT JOIN reviewers
ON reviewers.ID = reviews.reviewer_id
LEFT JOIN labels
ON labels.ID = reviews.band_id
ORDER BY artists.Artist_Name;

Options: ReplyQuote


Subject
Written By
Posted
Re: Using 4 tables, need a search on all of them, please help :-)
April 11, 2005 04:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.