MySQL with 2 LEFT JOINs on the same table

Question

Hi everyone.

I'm trying to run this query:

SELECT 
  Destaque.destaque, Noticia.id, Noticia.antetitulo, 
  Noticia.titulo, Noticia.lead, Noticia.legenda, 
  Noticia.publicacao, Seccao.descricao, Album.pasta,
  Foto.ficheiro, Foto.descricao, Cronista.nome, 
  Cronista.profissao, Cronista.ficheiro,
  AudioFile.*, AudioCollection.*, VideoFile.*, VideoCollection.*
FROM 
  nt_highlights AS Destaque
  LEFT JOIN nt_noticias  AS Noticia         ON Destaque.noticia_id = Noticia.id
  LEFT JOIN mm_fotos     AS Foto            ON Noticia.foto_id = Foto.id
  LEFT JOIN nt_temas     AS Seccao          ON Noticia.tema_id = Seccao.id
  LEFT JOIN mm_albuns    AS Album           ON Foto.album_id = Album.id
  LEFT JOIN nt_cronistas AS Cronista        ON Cronista.id = Noticia.cronista_id  
  LEFT JOIN ntNoticias_mmFiles AS Rel       ON Rel.noticia_id = Noticia.id
  LEFT JOIN mm_files     AS AudioFile       ON AudioFile.id = Rel.file_id
  LEFT JOIN mm_coleccoes AS AudioCollection ON AudioFile.coleccao_id = AudioCollection.id        
  LEFT JOIN mm_files     AS VideoFile       ON VideoFile.id = Rel.file_id
  LEFT JOIN mm_coleccoes AS VideoCollection ON VideoFile.coleccao_id = VideoCollection.id
WHERE 
  Destaque.area_id = 1
  AND Noticia.paraPublicacao = 1 
  AND Noticia.publicacao <= NOW()   
  AND (AudioFile.mimeType != '' OR AudioFile.id IS NULL)
  AND (VideoFile.mimeType = '' OR VideoFile.id IS NULL)
ORDER BY 
  Destaque.destaque

This will get me a number of articles (from nt_noticias) and the idea is to get at the same time a Video and an Audio file from the mm_files table.

What happens is that when I have an article with a sound and a video, MySQL will return 4 rows:

• with the sound (video is null)
• with the video (sound is null)
• with all nulls
• with the sound AND the video

How can I "force" it to return just one row per article with any existing video AND audio associated? What am I doing wrong here?

Answer 1

The JOIN will return all the combinations, that's the problem.
If you only have one audio and/or videofile per article then you might want to look at subselects. In SQL Server this would look something like (untested code):

SELECT title, 
       (select TOP 1 audio from audio where audio.aid = articles.id) as Audio, 
       (select TOP 1 video from video where video.aid = articles.id) as Video
FROM articles

Be careful that on large datasets this can perform poorly as the subselects in this example are executed individually for each row returned to the outer query. For example, if you return 10,000 articles then a total of 20,001 queries would actually be executed on the server. There are other possible answers to overcome this but they get more involved (I suspect you could do something with a derived table but it eludes me at the moment).

Answer 2

You probably want to optimize that join query into a view. It's a large query, and with that many joins, it's going to be pretty inefficient. Plus, a view helps you debug the joins and will basically simplifies by allowing you to write your joins (in the view) and the WHERE clause (in your select from the view) separately, which can help with debugging the queries.