MySQL – JOIN and Conditional Statements

join;MySQL

Can comeone help me to decipher the right logic of code needed for my query? I just can't find solutions about this or maybe my search queries are just vague. Lol.

I made a query and thought before it was fine but I just realized, just now, the inaccuracy of these. Here are what I've tried:

From a search page, for example we have this variable:

$variable = !empty($_POST["variable"]) ? $_POST['variable'] : "";

QUERY 1

SELECT
    ...
    ...
FROM table1
LEFT JOIN table2 ON table1.column_id = table2.column_id 
WHERE table2.column LIKE '%$variable%'

RESULT 1

  • If $variable has no value, only rows with respective data from table2 are displaying.
  • If $variable has a value, rows with respective data from table2 and matching value are displaying.

QUERY 2

SELECT
    ...
    ...
FROM table1
LEFT JOIN table2 ON table1.column_id = table2.column_id 
     AND table2.column LIKE '%$variable%'

RESULT 2

  • If $variable has no value, all rows are displaying.
  • If $variable has a value, all rows are still displaying.

DESIRED RESULT

  • If $variable has no value, all rows will be displayed.
  • If $variable has a value, rows with respective data from table2
    and matching value will be displayed.

I already tried using IF, CASE and LEFT OUTER JOIN but still no luck. Am I forgetting something with here? Or I'm doing it wrong (which is the truth)?

Best Answer

Okay, I've just gave another shot with IF clause and figured out the simplest query that I can think of. It's now giving the desired result, here's my query:

SELECT
    ...
    ...
FROM table1
LEFT JOIN table2 ON table1.column_id = table2.column_id 
WHERE IF('$variable' != '', table2.column = '$variable', 1 = 1)

Successfully, I placed that IF() clause in the right place. If you guys have thoughts about this code or have a very better one than this, kindly share it :)

[ADDITIONAL]

This is an answer from @ypercubeᵀᴹ and I love it!

SELECT
    ...
    ...
FROM table1
LEFT JOIN table2 ON table1.column_id = table2.column_id 
WHERE table2.column = '$variable' OR '$variable' = ''
Related Question