Case When with Leftjoin

Question

Can someone give me a hand, I’ve spent hours trying to make this query work:

SELECT p.id as idPedido, p.*, i.*, e.*, f.*, m.id AS idEmpresa, m.razao_social, l.id AS idFilial, l.razao_social AS rsf, c.id, c.nome
            FROM wu_pedido AS p
            LEFT JOIN wu_pedido_itens AS i ON (p.id = i.pedido_id)

            (CASE WHEN i.tipo_empresa = 'M' THEN
                LEFT JOIN wu_produto_empresa AS e ON (i.empresa_id = e.id)
                LEFT JOIN wu_empresa AS m ON (e.empresa_id = m.id)
            ELSE i.tipo_empresa = 'F' THEN
                LEFT JOIN wu_produto_filial AS f ON (i.empresa_id = f.id)
                LEFT JOIN wu_filial AS l ON (f.filial_id = l.id)
            END)

            LEFT JOIN wu_categoria_combobox AS c ON (p.status_pedido_id = c.id)

            GROUP BY p.id ORDER BY p.id ASC LIMIT :start, :limit

Answer 1

You may not use the CASE to decide which JOIN will do. Instead do both queries separate and use a UNION:

SELECT x.*
  FROM (
  SELECT p.id AS idpedido,
         p.*,
         i.*,
         e.*,
         f.*,
         m.id AS idempresa,
         m.razao_social,
         l.id AS idfilial,
         l.razao_social AS rsf,
         c.id,
         c.nome
    FROM wu_pedido AS p
    LEFT JOIN wu_pedido_itens AS i
      ON p.id = i.pedido_id
    LEFT JOIN wu_produto_empresa AS e
      ON i.empresa_id = e.id
    LEFT JOIN wu_empresa AS m
      ON e.empresa_id = m.id
    LEFT JOIN wu_categoria_combobox AS c
      ON p.status_pedido_id = c.id
   WHERE i.tipo_empresa = 'M'
   UNION
  SELECT p.id AS idpedido,
         p.*,
         i.*,
         e.*,
         f.*,
         m.id AS idempresa,
         m.razao_social,
         l.id AS idfilial,
         l.razao_social AS rsf,
         c.id,
         c.nome
    FROM wu_pedido AS p
    LEFT JOIN wu_pedido_itens AS i
      ON p.id = i.pedido_id
    LEFT JOIN wu_produto_filial AS f
      ON i.empresa_id = f.id
    LEFT JOIN wu_filial AS l
      ON f.filial_id = l.id
    LEFT JOIN wu_categoria_combobox AS c
      ON p.status_pedido_id = c.id
   WHERE i.tipo_empresa = 'F'
  ) x
GROUP BY  x.id
ORDER BY  x.id ASC limit :start,
          :limit