mySQL Join help needed

[w0ng3r]

I'm currently using "beginning php apache mysql web developement" to learn php, and there is an exercise that is leaving me kinda stumped

 

I am given the following tables:

 

movie:

 

+-----------+-----------------+----------------+-------------------+
| people_id | people_fullname | people_isactor | people_isdirector |
+-----------+-----------------+----------------+-------------------+
|         1 | Jim Carrey      |              1 |                 0 |
|         2 | Tom Shaydac     |              0 |                 1 |
|         3 | Lawrence Kasdan |              0 |                 1 |
|         4 | Kevin Kline     |              1 |                 0 |
|         5 | Ron Livingston  |              1 |                 0 |
|         6 | Mike Judge      |              0 |                 1 |
+-----------+-----------------+----------------+-------------------+

 

people table

+----------+----------------+------------+------------+-----------------+----------------+
| movie_id | movie_name     | movie_type | movie_year | movie_leadactor | movie_director |
+----------+----------------+------------+------------+-----------------+----------------+
|        1 | Bruce Almighty |          5 |       2003 |               1 |              2 |
|        2 | Office Space   |          5 |       1999 |               5 |              6 |
|        3 | Grand Canyon   |          2 |       1991 |               4 |              3 |
+----------+----------------+------------+------------+-----------------+----------------+

 

I need to use join to come up with the following table:

 

+----------------+-----------------+----------------+
| movie_name     | movie_leadactor | movie_director |
+----------------+-----------------+----------------+
| Bruce Almighty | Jim Carrey      | Tom Shaydac    |
| Office Space   | Ron Livingston  | Mike Judge     |
| Grand Canyon   | Kevin Kline     | Lawrence Kasda |
+----------------+-----------------+----------------+

 

The closest i can get is to have moviename and movie lead actor display correctly, the movie director field would just have the actor names repeated, using:

SELECT movie_name, people_fullname movie_leadactor, people_fullname movie_director FROM movie LEFT JOIN people ON movie_leadactor = people_id;

(I've tried adding and movie_director = people_id by then for obvious reasons the query returns a empty set).

Can anyone tell me whut I is doing wrong?

 

TIA

[premiso]

With a query like this, you will need to use sub-queries to create a view of sorts, if either of those terms are foreign to you, I would look them up. Given that this is from a book it should have already explained that...

 

So let me explain what is going on, since you have 2 id's in the movie table that you need to fetch from 1 table you have to sort of run 2 separate queries, since for the obvious you cannot pull out 2 different rows from 1 table in 1 query persay.  What you have to do is pull the data and create a "view" what a view is basically a table created from current data in the database and it acts like a temporary table for you to be able to use to pull some data out of like it was coming out of a table.

 

SELECT movie_name, lead.movie_leadactor, director.movie_director FROM 
(SELECT people_id, people_fullname AS movie_leadactor FROM people WHERE people_isactor = 1) lead, 
(SELECT people_id, people_fullname AS movie_director FROM people WHERE people_isdirector = 1) director, 
movie m WHERE m.movie_leadactor = lead.people_id AND m.movie_director = director.people_id;

As you can see this query is pretty complicated, so let me explain it a bit. The first SELECT after the FROM is where we are getting the leadactor from, note we aliased the table "lead", but basically this just pulls id and the name of the actor as the leadactor, pretty simple so it is like we have a table called "lead" setup which has people_id, people_fullname pulled from people where isactor is set to 1. Pretty basic stuff there, the second query does much of the same, but only pulls the director.

 

Now onto the WHERE clause, basically we take the movie table and join it to our "lead" and "director" table using the actor/director people_id. And then viola, we have our table like it was meant to be done. As to what you were doing wrong, well you needed to dig more into this, as it is not a simple query like some people would think it to be due to the simple fact that you have to pull 2 rows from a single table per query, which does require some unusual tactics.

 

EDIT: I hope this was not a homework assignment.

[JustLikeIcarus]

Heres one way to do by bringing in the people table twice.

 

SELECT movie_name, actors.people_fullname, directors.people_fullname
FROM movie, people actors, people directors
WHERE actors.people_id = movie_leadactor
AND directors.people_id = movie_director

[premiso]

Heres one way to do by bringing in the people table twice.

 

Nice, did not know you could do that That is probably better then my way. Good work!

[JustLikeIcarus]

Heres one way to do by bringing in the people table twice.

 

Nice, did not know you could do that That is probably better then my way. Good work!

 

Haha thanks.  Yeah it should work fine, once a table is given an alias its essentially its own table and can be treated as such.  Just one of the 100 or so ways this query can be done.  Gotta love SQL.