Home / Questions / 285454
Accepted
akko
Asked: 2020-08-13 12:49:03 +0800 CST 2020-08-13 12:49:03 +0800 CST

How to include a conditional in the WHERE only if a JOIN has something?

  • 772

I need help to perform the following query in MySQL, I am joining two tables with a LEFT JOINto return results in case the second table does not contain information.

SELECT
  e.emp_id AS "empresa_id",
  et.idioma_id AS "idioma"
FROM empresa as e
LEFT JOIN empresa_translate as et
  on et.emp_id = e.emp_id
WHERE
  replace(replace(e.emp_nombre,'z','s'),'h','')
  LIKE replace(replace('%NOMBRE%','z','s'),'h','');

This gets the results regardless of whether the table empresa_translatehas any records associated with the id, however I need to implement the following logic to what I already have.

If there is a record in the table empresa_translateassociated with this id, it filters the results to show only those with a et.idioma_id = 1.

SELECT
  e.emp_id AS "empresa_id",
  et.idioma_id AS "idioma"
FROM empresa as e
LEFT JOIN empresa_translate as et
  on et.emp_id = e.emp_id
WHERE
  replace(replace(e.emp_nombre,'z','s'),'h','')
  LIKE replace(replace('%NOMBRE%','z','s'),'h','')
  AND et.idioma_id = 1;

The problem with doing it this way is that it doesn't return any results. What is the correct way to do this?

mysql

1 Answers

  1. Best Answer
    SELECT  e.emp_id AS "empresa_id",
    et.idioma_id AS "idioma"
    FROM empresa as e
    LEFT JOIN empresa_translate as et
        on et.emp_id = e.emp_id
    WHERE   replace(replace(e.emp_nombre,'z','s'),'h','')
        LIKE replace(replace('%NOMBRE%','z','s'),'h','')
        AND (
            ( et.emp_id IS NOT NULL AND et.idioma_id = 1)
            OR
            ( et.emp_id IS NULL)
        )

    Basically there are two conditions to check:

    1. If you have a related row in empresa_translate, we further ask thatet.idioma_id = 1
    2. Otherwise, fetch the unrelated rows: et.emp_id IS NULL

    Or the more compact form

     AND (
        et.emp_id IS NULL OR ifnull(et.idioma_id,0) = 1
     )
    • 5