making a where clause optional in a sql statement

Question

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.

score 1 · Answer 1 · answered Apr 20 '15 at 20:22

You may add the WHERE clause in the LEFT JOIN so not joining 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`

making a where clause optional in a sql statement

1 Answers1