need help with simple PHP+SQL problem
Posted: Tue Dec 08, 2009 8:04 am
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:
I need to use join to come up with the following table:
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
I am given the following tables:
movie:
Code: Select all
+-----------+-----------------+----------------+-------------------+
| 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 |
+-----------+-----------------+----------------+-------------------+Code: Select all
+----------+----------------+------------+------------+-----------------+----------------+
| 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 |
+----------+----------------+------------+------------+-----------------+----------------+Code: Select all
+----------------+-----------------+----------------+
| movie_name | movie_leadactor | movie_director |
+----------------+-----------------+----------------+
| Bruce Almighty | Jim Carrey | Tom Shaydac |
| Office Space | Ron Livingston | Mike Judge |
| Grand Canyon | Kevin Kline | Lawrence Kasda |
+----------------+-----------------+----------------+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