Using IF statement in a SELECT WHERE

[gmckayau]

I have a table of updates that I wish to compare against the a database to find those records with differences.
Each update record has a cat_id.
Some updates have an alt_cat_id.
I need a select to search to search if these differ, but only search for the alt-cat_id if it is populated. I don't have a solution for this but thought the IF statement might help.

SELECT upd.key, upd.cat_id, upd.alt_cat_id, db.cat_id FROM db
JOIN update ON (upd.key = db.key)
WHERE upd.cat_id <> db.cat_id
OR upd.alt_cat_id NOT IN (SELECT cat_id FROM cats WHERE cats.key = upd.key);

The OR only needs to be executed if alt_cat_id is non-blank....

 

[IanWaterman]

SELECT upd.key, upd.cat_id, upd.alt_cat_id, db.cat_id FROM db
JOIN update ON (upd.key = db.key)
WHERE upd.cat_id <> db.cat_id
OR
(upd.alt_cat_id is not null and upd.alt_cat_id NOT IN (SELECT cat_id FROM cats WHERE cats.key = upd.key))

Ian

 

[gmckayau]

Thank you. Now that I look at it, it seems obvious...