CODEWITHSUNDEEP
phpmysqldatabaseopencart
Mark
Mark

Reputation: 402

select value of column doesnt exist

I have this php query;

$sql = "SELECT * 
        FROM store AS s 
        LEFT JOIN setting as set ON (s.store_id = set.store_id)";
$sql .= " WHERE (set.key = 'config_template' OR set.key IS NULL)";    
$sql .= " ORDER BY url";

$query = $this->db->query($sql);

What i would like to do is return all rows where key is equal to "config_template". But if there are no keys that match "config_template", I still want to return that row except the column key would be empty.

I thought about using NOT IN or NOT EXIST but I am not sure how to use these in this circumstance?

Upvotes: 0

Views: 103

Answers (2)

user330315
user330315

Reputation:

Moving the condition for the key into the JOIN condition should do the trick:

SELECT * 
FROM store AS s 
  LEFT JOIN setting as set 
         ON s.store_id = set.store_id AND set.key = 'config_template'
ORDER BY url

Upvotes: 1

Yogesh Suthar
Yogesh Suthar

Reputation: 30488

the problem is here. 

$sql .= " WHERE (set.key = 'config_template' OR set.key IS NULL)";

In this where clause either both condition will be true or false, but it will fail when both condition will be false.

Try this,

$sql .= " WHERE (set.key = 'config_template' OR set.key IS NULL) OR/AND SECOND_CONDITION ";

In this way you will definitely get result.

Upvotes: 1

Related Questions