CODEWITHSUNDEEP
phpmysql
Multitut
Multitut

Reputation: 2169

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.

Upvotes: 1

Views: 64

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133400

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

Upvotes: 1

Related Questions