MySQL query question

Advertisement
Hi All,
I've got 2 MySQL tables, movie and movie_tag. The movie table contains a movie_id column and the movie_tag table contains 2 columns, movie_id and tag_id. I know how to correctly join them, what I would like to know is if I have this query:
SELECT * FROM movie LEFT JOIN movie_tag USING movie_id
and save it as a result set in a language like PHP or Python can I:
1. Loop through and only display each movie_id once, BUT...
2. Also display all the tag_id's without nested-looping.
I.e. I would like to list then something like:
-- movie_id_1
|-- tag_id_1
|-- tag_id_2
-- movie_id_2
|-- tag_id_1
Thanks for any help you can give!
Advertisement

Replay

Your query will give you the cartesian product of movie and movie_tag, which means (given your example list) the output rows will look like this:
movie_id_1 | tag_id_1
movie_id_1 | tag_id_2
movie_id_2 | tag_id_1
You can then create the list you've shown above by post-processing in your procedural language of choice: loop over the results, track when movie_id changes, indent accordingly.