Unless in T-SQL

Transact-SQL
1

Anyone know if it's possible to code this as part of a WHERE clause of a SELECT?
Text:
WHERE
DEGEST must exist where COM is between 01/08/15 and 31/07/16
and AIM is in (020, 021 or 031) unless CTRY exists

I can code it ok until the last part i.e. coding the 'unless CTRY exists

Any help appreciated
Alan

2

Select
case
when YourColumnField = 'CTRY' then 'CTRY'
when AIM in (020,021,031) then 'What ever you want to call it'
End as Name
,*
from yourtable
Where
YourColumnField = 'DEGEST'
and COM between '20150801' and '20160731'
and (YourColumnField = 'CTRY' or AIM in (020,021,031))

3

Or maybe this is what you want

Select 'DEGEST' as Flag,* from yourtable
Where YourColumnField = 'DEGEST'
and COM between '20150801' and '20160731'
and AIM in (020,021,031)
Union all
select 'CTRY' as Flag,* from yourtable
Where YourColumnField = 'CTRY'

4

If non of the above works for you, please provide:

  • table descriptions as create statements
  • sample data as insert statements
  • expected output from the sample data you provide
5

Also you can use where with field in(sqlquery)

WHERE
DEGEST in ( select degest from yourtabledegest
where yourtabledegest.COM between 01/08/15 and 31/07/16)
and AIM is in (020, 021 or 031)
and CTRY not in (select CTRY FROM yourtablectry.CTRY
WHERE XXXXXXXX)

I can code it ok until the last part i.e. coding the 'unless CTRY exists