Xtreme Biker Xtreme Biker - 6 months ago 15
SQL Question

MySql XNOR equivalent

I'm writing a SQL query which checks for incorrect equipment in its

where
clause. In order to perform this operation I need to check each of the equipment's revision cycles one by one, so this clause would limit the results to correct equipment (not having any of the cycles incorrect):

where not exists(select * from trevision_cycle cycle
where cycle.id_equipment = equip.id and cycle.`status` = 'NO_OK')


However, now I want to introduce a query parameter in order to retrieve only the correct equipment or only the incorrect equipment, depending on its value. Let's say I call it
incorrect
, so I would like to do the same as what's done here:

where (not incorrect and not exists(select * from trevision_cycle cycle
where cycle.id_equipment = equip.id and cycle.`status` = 'NO_OK'))
or (incorrect and exists (select * from trevision_cycle cycle
where cycle.id_equipment = equip.id and cycle.`status` = 'NO_OK'))


So, if the
incorrect
flag is present, check for equipment having at least one incorrect revision cycle. Otherwise, retrieve only the equipment having all of them correct. The query looks quite redundant though and the same result could be achieved with a logical XNOR.

Do I have a better choice for this?

Update

Sample data

Equipment 1 -> Has one NO_OK cycle
Equipment 2 -> All its cycles are OK

Query with incorrect = true -> Returns only Equipment 1
Query with incorrect = false -> Returns only Equipment 2

Answer

Referencing This Fellow's blog, if you were to create the function XNOR like so:

--XNOR
CREATE FUNCTION XNOR (@a bit, @b bit) RETURNS bit AS
BEGIN
    RETURN @a ^ @b ^ 1
END

Then you could simplify your statement using it as so:

where dbo.XNOR(incorrect, exists(select * from trevision_cycle cycle 
        where cycle.id_equipment = equip.id and cycle.`status` = 'NO_OK')

EDIT: My apologies, for MySQL, you would have to create the function using its XOR instead of '^' ('^' is the XOR function for SQL Server). I'm uncertain if the rest of the syntax is correct for MySQL, but you get the idea:

--XNOR
CREATE FUNCTION XNOR (@a bit, @b bit) RETURNS bit AS
BEGIN
    RETURN @a XOR @b XOR 1
END

If you have reason not to make up a new function to do this, then the following is logically equivalent:

where incorrect XOR exists(select * from trevision_cycle cycle 
        where cycle.id_equipment = equip.id and cycle.`status` = 'NO_OK' XOR 1