CODEWITHSUNDEEP
mysqlsql
somejkuser
somejkuser

Reputation: 9040

making a where clause optional in a sql statement

Here is my current query:

SELECT `performers`.`hash`,
        `performers`.`alias`,
        `performers`.`date_updated`,
        `performers`.`status`, 
        IF(`performers`.`status` = 'active', 'deleted','active') AS `statususe`,
        `images`.`image_hash_file` 
FROM `performers` 
LEFT JOIN `images` ON `images`.`asset_id` = `performers`.`id` 
WHERE (`images`.`asset_type` = 'performer') 
ORDER BY `alias` ASC LIMIT 12`

In it, there is a where clause 

WHERE (`images`.`asset_type` = 'performer')

I'd like it to be optional, such that if there where clause doesn't fit it still shows the records from the performers table that do not have a join to the fulfilling images records.

Upvotes: 1

Views: 238

Answers (3)

Eric Ly
Eric Ly

Reputation: 2133

You may add the WHERE clause in the LEFT JOIN so not matching rows are still in the result set.

SELECT `performers`.`hash`, `performers`.`alias`, 
  `performers`.`date_updated`, `performers`.`status`,
  IF(`performers`.`status` = 'active', 'deleted','active') AS `statususe`,
  `images`.`image_hash_file`
FROM `performers`
LEFT JOIN `images` ON `images`.`asset_id` = `performers`.`id` AND `images`.`asset_type` = 'performer'
ORDER BY `alias` ASC
LIMIT 12

The main difference between WHERE and ON clause is that the ON clause works during the JOIN when the WHERE clause works after everything have been joined making not matching rows to be removed from the result set.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You should move the where condition to the on clause:

SELECT p.`hash`, p.`alias`, p.`date_updated`, p.`status`, 
       IF(p.`status` = 'active',  'deleted', 'active') AS statususe,
       i.`image_hash_file` 
FROM `performers`  p LEFT JOIN
     `images`i
     ON i.`asset_id` = p.`id` and
        i.`asset_type` = 'performer' 
ORDER BY `alias` ASC
LIMIT 12

I also modified the query to use table aliases. They make queries much easier to write and to read.

Upvotes: 1

ericpap
ericpap

Reputation: 2937

Try something like this:

WHERE (`images`.`asset_type` = 'performer' OR `images`.`asset_type` IS NULL)

Upvotes: 1

Related Questions