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.

So you're kinda looking for "Clue, the movie, starring Christopher Plummer as Colonel Mustard, in the Study with the Candlestick (made by Tiffany)"? — Marc B
– Marc B, Commented Jul 21, 2011 at 17:13
Yes ideally I want to display for example an item, worn in which film, the actor wearing it, and the brand of the item etc.... — Martin Dye
– Martin Dye, Commented Jul 21, 2011 at 17:16

Phill Pafford · Accepted Answer · 2011-07-21 17:39:13Z

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';

Collectives™ on Stack Overflow

Querying multiple tables using mysql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related