Group by clause return latest row information
- by I Like PHP
below is my table structure
table_movie_info
i_movie_id |movie_actor_id |movie_actress_id |movie_director_id | movie_producer_id
48 | 5 | 9 | 66 | 21
48 | 6 | 15 | 88 | 22
48 | 7 | 12 | 77 | 23
one more table is
table_movie
movie_id | movie_year | movie_genre_id |movie_rating
1 | 2009 | 6 | 8
2 | 2001 | 5 | 7.5
48 | 2007 | 3 | 6.8
now i need total movie information using both table,i write below query
SELECT * FROM table_movie_info LEFT JOIN table_movie ON movie_id = i_movie_id
WHERE i_movie_id=48 GROUP BY i_movie_id
above query return only one row , but i need such type of information
movie_id=48,
actors_id list=5,6,7
acttress_id list=9,15,12
etc..
please tell me the optimized query which h return complete information i need.
thanks for helping me always.
