Multitut Multitut - 6 days ago 6
MySQL Question

MySQL Conditional LEFT JOIN

I have two tables with a one to many relationship. Place and Promo. A place can have several promos, which apply by day of the week.

Simplifying (most columns are omitted), These are the tables:

Place

idnum nombre
--
42 SUBWAY
55376 ANTOJERIA MAKECH
50112 TORTAS BERNAL LAS ORIGINALES DESDE 1960
55185 LA MARINERA


Promo

idnum titulo dia idcliente
135 Pescado Frito 2 x 1 Lunes 55185
136 Pescado Frito 2 x 1 Martes 55185
137 Margaritas 2 x 1 Jueves 55185
138 Tacos 3 x 2 Viernes 55185
139 5 cervezas Sabado 55185


I want to build a Query that gets me all the Place results, and if available, gets me the corresponding Promo. This is the query I have so far:

SELECT * FROM (SELECT d.nombre, a.dia, a.descripcion FROM directorio as d LEFT JOIN avisos as a ON d.idnum = a.idcliente WHERE palabras LIKE "%Marinera%" LIMIT 15 OFFSET 0) AS t WHERE t.dia = "Sabado" OR t.dia IS NULL;


It works just fine when dia matches the query (in this case "Sabado") or when dia is NULL (for when the place has no promos); but the problem comes when the place does have promos but none of them matches dia... in that case I would like to still get the record with null values on the promo columns.

Answer

If you want the response also when the dia != form "Sabado" then you can use a CASE WHEN

  SELECT * 
  FROM (
      SELECT d.nombre, case when a.dia = "Sabado"  then a.dia else NULL END, a.descripcion 
      FROM directorio as d 
      LEFT JOIN avisos as a ON d.idnum = a.idcliente 
      WHERE palabras LIKE "%Marinera%" LIMIT 15 OFFSET 0) AS t 
  WHERE t.dia = "Sabado" 
  OR t.dia IS NULL;

this with max(dia) for remove duplicated row

  SELECT 
        idnum
      , nombre
      , MAX(dia) as dia
      , descripcion 
  FROM ( 
        SELECT 
              d.idnum
            , d.nombre
            , case when a.dia = "Domingo" then a.dia else NULL END as dia
            , a.descripcion FROM directorio as d 
      LEFT JOIN avisos as a ON d.idnum = a.idcliente 
      WHERE palabras LIKE "%Marinera%"
      LIMIT 15 OFFSET 0) AS t 
  WHERE t.dia = "Domingo" OR t.dia IS NULL
Comments