mysql : a pivot table handler request?

I have a mysql question :

using php with mysql I actually have a schema that looks like this :

3 tables :

module

module_has_theme (pivot table)

theme

The idea behind this is to display the modules of my application using themes if they have, and if they don't, then display them in an extra category (other).

So let's say if I populate my module table with some modules : mod1, mod2, mod3, mod4, mod5

Then I create some themes : fruit, animals, number, human

Then in the pivot table I bind mod1 with animals.

Then I would like to have the result of the开发者_运维问答 request like this (or something similar)

result :

fruit => null,

animals => mod1,

number => null,

human => null,

other => mod2, mod3, mod4, mod5

Actually, my code use 2 mysql requests and a php loop to do it,

(

first request takes all modules

second request takes all modules that have themes (using two inner join)

a php loop that organize modules the way I want using the two above resulting arrays

)

Now I was wondering if there would be a magic mysql request that would do the same job in 1 single request ?

Please if you know that let me know ;)

Have a read up on inner and outer joins

Try something like...

SELECT module AS M
INNER JOIN module_has_theme AS MT
ON M.module_id = MT.module_id
LEFT JOIN theme AS T
ON T.theme_id = MT.theme_id

Its best to run queries like this direct in a MySQL client such as toadmysql in order to test you are getting the right results.

The AS M, AS MT, AS T are aliases for the table names to help keep your query shorter. Good luck