Querying multiple tables using mysql

Question

I am trying to query 5 separate tables in my mysql database, the structures are as follows;

item
itemid | item | description | brand | date | time | path |

actor
actorid | name | actorthumb | bio |

brand
brandid | brandname | description | image |

movie
movieid | title | genre | year | moviethumb | synopsis|

request
requestid | userid | itemid | brandid | movieid | actorid | content | requestdate |

Currently I can join 2 of the tables and display the information I need, for example an item, who wears it :

$query = "SELECT * FROM actor, request WHERE actor.actorid = request.actorid and itemid = ".$itemid; 

and in what film, using

$query = "SELECT distinct * FROM movie, request WHERE movie.movieid = request.movieid and itemid = ".$itemid;

However I need to write 1 query that will display the data from all 5 tables and I can display what I need from these.

I think I need to use the JOIN command however I am not sure how to use this?

Please advise.

Answer 1

This is a very simple query structure to show you how to reference the different tables using their id's. It can be greatly improved upon depending on the results you want

SELECT 
i.*, /* This will display all the fields in the item table */
a.*, /* This will display all the fields in the actor table */
b.*, /* This will display all the fields in the brand table */
m.*, /* This will display all the fields in the movie table */
r.*  /* This will display all the fields in the request table */
FROM item AS i, actor AS a, brand AS b, movie AS m, request AS r
/* This joins the request table itemid with the item table itemid */
WHERE r.itemid = i.itemid 
/* This joins the request table actorid with the actor table actorid */
AND r.actorid = a.actorid
/* This joins the request table brandid with the brand table brandid */
AND r.brandid = b.brandid
/* This joins the request table movieid with the movie table movieid */
AND r.movieid = m.movieid

If you wanted to return a more filtered result set you could add something like this:

/* Or whatever the id is */
AND r.requestid = 123 

Example:

SELECT 
i.item, i.description, i.brand, /* item table */
a.name, /* actor table */
b.brandname, b.description, b.image, /* brand table */
m.title /* movie table */
FROM item AS i, actor AS a, brand AS b, movie AS m, request AS r
/* This joins the request table itemid with the item table itemid */
WHERE r.itemid = i.itemid 
/* This joins the request table actorid with the actor table actorid */
AND r.actorid = a.actorid
/* This joins the request table brandid with the brand table brandid */
AND r.brandid = b.brandid
/* This joins the request table movieid with the movie table movieid */
AND r.movieid = m.movieid
AND a.name = 'Rosie Huntington-Whiteley';