Arch Linux

addox

Member

From: Leeds, UK

Registered: 2008-08-06

Posts: 6

MySQL query question

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!

tam1138

Member

Registered: 2007-09-10

Posts: 238

Re: MySQL query question

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.