output sql statement is grouped

Question

Hello everybody,

I have a little problem, have the following statement in mysql:

SELECT cmfilm.*, cmgenre.titel AS genretitel, cmvertoning.*, cmzaal.titel AS zaaltitel 
FROM cmfilm 
      LEFT JOIN cmvertoning ON cmvertoning.film_id=cmfilm.id 
      LEFT JOIN cmgenre ON cmfilm.genre_id=cmgenre.id 
      LEFT JOIN cmzaal ON cmvertoning.zaal_id=cmzaal.id 
WHERE cmvertoning.id IN(74,74,74,74) 
ORDER BY cmfilm.id ASC

because i'm asking the same id 4 times it only gives me 1 result. But i would like to have the result to be returned 4 times, as i asked it to do. Anyone who knows how to solve this?

Answer 1

You did not ask for 4 rows, you asked for any rows where id is 74, 74, 74 or 74 - of which there is only one.

The real question is, why do you want the same row 4 times?

Answer 2

Maybe it would be better to achieve this on the application side - put returned rows in a map, then iterate over the ids you wanted and get appropriate rows from a map. Because there is another problem - the rows might not be in the order in which order you specified the ids (BTW. this row ordering problem can be solved also using field).

The UNION solution would work too. But, honestly, I think that you have some design problem if you need to fetch the same rows multiple times.

Answer 3

I don't think you can acomplish that without some sort of a loop, or by using the UNION ALL statement.