MySQL: JOIN and Conditional Statement

Question

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 (something I found somewhere) but still no luck. Am I forgetting something with here? Or I'm doing it wrong (which is the truth)?

I have an alternative way and that is using conditional statement with PHP (which will be my last option) but if this thing is possible with MySQL only then it's a hooray.

Answer 1

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' = ''

Answer 2

    SELECT CP.*,CT.* FROM `ChecklistTasks` AS CT LEFT JOIN ChecklistPoints AS CP 
    ON CT.`pkTID` = CP.`fkTID` AND CP.`CheckPointName` LIKE '%var%'

OR

    SELECT CP.*,CT.* FROM `ChecklistTasks` AS CT LEFT JOIN ChecklistPoints AS CP 
    ON CT.`pkTID` = CP.`fkTID` AND CP.`CheckPointName` LIKE '%%'

So far your query is right .. it is processing correct result..you want

Answer 3

You need to check through condition for join, must use inner join

$sql = "SELECT
...
...FROM table1";

if(!empty($variable)){
$sql = $sql." INNER JOIN table2 ON table1.column_id = table2.column_id WHERE table2.column LIKE '%$variable%'";
}

Answer 4

You can use something like:

SELECT
    ...
    ...
FROM table1
LEFT JOIN table2 ON table1.column_id = table2.column_id 
     AND (table2.column LIKE '%$variable%' OR $variable = '' OR $variable IS NULL)

If $variable is NULL or empty, then all records will be returned by the query. Otherwise, only matching records are returned.