user1392897 user1392897 - 28 days ago 8
SQL Question

SQL Check for Duplicate Records with Two Keys

Is it possible to combine these two statements into a single query?

There are two columns (a_id and b_id) against which the other 2 columns need to be unqiue.

SELECT * FROM cases WHERE a_id=:a_id AND case_form=:case_form AND case_type=:case_type

SELECT * FROM cases WHERE b_id=:b_id AND case_form=:case_form AND case_type=:case_type


..if any results returned alert user to duplicate case

Answer

Two quick approaches:

SELECT * FROM cases WHERE a_id=:a_id AND case_form=:case_form AND case_type=:case_type
UNION ALL
SELECT * FROM cases WHERE b_id=:b_id AND case_form=:case_form AND case_type=:case_type

or (no pun intended!)

SELECT * FROM cases 
WHERE (a_id=:a_id AND case_form=:case_form AND case_type=:case_type) 
   OR (b_id=:b_id AND case_form=:case_form AND case_type=:case_type)