9 replies to this topic

[cakka]

hello,

i have 2 tables

first table : movie

+----------+----------------+------------+------------+-----------------+----------------+
| 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 |
+----------+----------------+------------+------------+-----------------+----------------+

second table : people

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


i want to display :

movie name + movie actor + movie director
Bruce Almighty + Jim Carrey + Tom Shadyac
.. + ... + ..

what the query to do this in mysql ?
i am really confuse

thanks

[fayyazlodhi]

Aren't you suppose to have a primary and a foreign key i.e. one field in first table that can relate it's records into second table so that a join can be applied?

[WingedPanther]

You'll have to use two joins from the movie table to the people table.

[cakka]

WingedPanther said:

You'll have to use two joins from the movie table to the people table.

yes, i have do.
but i am confuse how the join code
can you give me know ?
thanks

[cakka]

fayyazlodhi said:

Aren't you suppose to have a primary and a foreign key i.e. one field in first table that can relate it's records into second table so that a join can be applied?

i think no
thanks

[WingedPanther]

select * from movie left outer join people actors on movie.movie_leadactor = actors.people_id left outer join people directors on movie.move_director = directors.people_id

[cakka]

WingedPanther said:

select * from movie left outer join people actors on movie.movie_leadactor = actors.people_id left outer join people directors on movie.move_director = directors.people_id

i have try your code, and get

+----------+----------------+------------+------------+-----------------+----------------+-----------+-----------------+----------------+-------------------+-----------+-----------------+-------------

---+-------------------+

| movie_id | movie_name     | movie_type | movie_year | movie_leadactor | movie_director | people_id | people_fullname | people_isactor | people_isdirector | people_id | people_fullname | people_isact

or | people_isdirector |

+----------+----------------+------------+------------+-----------------+----------------+-----------+-----------------+----------------+-------------------+-----------+-----------------+-------------

---+-------------------+

|        1 | Bruce Almighty |          5 |       2003 |               1 |              2 |         1 | Jim Carrey      |              1 |                 0 |         2 | Tom Shadyac     |

 0 |                 1 |

|        2 | Office Space   |          5 |       1999 |               5 |              6 |         5 | Ron Livingston  |              1 |                 0 |         6 | Mike Judge      |

 0 |                 1 |

|        3 | Grand Canyon   |          2 |       1991 |               4 |              3 |         4 | Kevin Kline     |              1 |                 0 |         3 | Lawrence Kasdan |

 0 |                 1 |

+----------+----------------+------------+------------+-----------------+----------------+-----------+-----------------+----------------+-------------------+-----------+-----------------+-------------

---+-------------------+

it's still wrong

any ideas ?
i am stuck on this code :(

[WingedPanther]

How is that wrong? What are you expecting? I thought you wanted the actor and director, which is what is being returned.

[cakka]

WingedPanther said:

How is that wrong? What are you expecting? I thought you wanted the actor and director, which is what is being returned.

it was display all field that related with that
but very thanks
i have got the solution for this problem

[yassinebelkaid]

I don't see any shared key, it has to be one to make the JOIN.