Switch Statement in WHERE Clause

[Gray]

Hi

Been trying to guess this for hours...

I want to count the number of records returned in a SELECT subquery and alter the WHERE of the main query accordingly...I know this should be possible.. So in English... (System_Record is a Boolean)...

if records returned by SELECT = 0 then set the WHERE condition " OR System_Record=TRUE"



if records returned by SELECT > 0 then set the WHERE condition " AND System_Record=FALSE"

My code so far.. don't laugh! The QBE did!

SELECT 
TBL.Item_Name,
TBL.Unique_No,
TBL.ID, 
(SELECT COUNT(UNIQUE_NO) FROM ADDRS_TBL AS TBL1 
WHERE TBL1.ID=75666) As My_Count 
FROM ADDRS_TBL AS TBL 
WHERE (TBL.ID=75666) 
SWITCH(My_Count=0, " OR System_Record=True", My_Count>0, " AND System_Record=False")

Yikes!! Can someone help please?

[Gray]

Hi

Sussed it!... For anyone else reading, this seems to work for me....

SELECT 
TBL.Item_Name,
TBL.Unique_No,
TBL.ID,
TBL.System_Record
FROM Addrs As TBL 
WHERE (TBL.ID=75666 AND EXISTS( SELECT * FROM ADDRS As TBL1 WHERE TBL1.ID=75666)) OR (System_Record=True AND NOT EXISTS( SELECT * FROM ADDRS As TBL1 WHERE TBL1.ID=75666) )

When an invalid ID is used, my system_record is retrieved... when a valid one is used results are as expected...