I am trying to write a query that returns all suppliers within a given range
that either do not have any insurance (appear only in Suppliers table) or
Suppliers where the insurance has expired from a given date

Eg 4 Suppliers
Supplier1 - no insurance
Supplier2 - insurance expired
Supplier3 - insurance current
Supplier4 - not in range

The Supplier range is 'where AccRef like '^SC%'
The Expiry Date is less than or equal to '20071130'

The result set would include Supplier1 because it is not in the
InsuranceDetails table at all and Supplier2 because the insurance has
expired.

How can I do this in one query?

I have included some SQL for your info

Thanks

A

CREATE TABLE [dbo].[Suppliers](

[AccRef] [nvarchar](8) NOT NULL,

[AccName] [nvarchar](30) NOT NULL

) ON [PRIMARY]



CREATE TABLE [dbo].[InsuranceDetails](

[AccRef] [nvarchar](8) NOT NULL,

[DateExpire] [datetime] NOT NULL

) ON [PRIMARY]

INSERT INTO dbo.Suppliers ([AccRef], [AccName])
SELECT '^SC100' As Expr1, 'Supplier1' as Expr2
INSERT INTO dbo.Suppliers ([AccRef], [AccName])
SELECT '^SC200' AS Expr1, 'Supplier2' as Expr2
INSERT INTO dbo.Suppliers ([AccRef], [AccName])
SELECT '^SC300' AS Expr1, 'Supplier3' as Expr2
INSERT INTO dbo.Suppliers ([AccRef], [AccName])
SELECT '10000' AS Expr1, 'Supplier4' as Expr2


INSERT INTO dbo.InsuranceDetails ([AccRef], [DateExpire])SELECT '^SC300' AS
Expr1, '20080331' as Expr2
INSERT INTO dbo.InsuranceDetails ([AccRef], [DateExpire])SELECT '^SC200' AS
Expr1, '20071101' as Expr2

There are a variety of ways you can do this. Here is one:

SELECT s1.accref, s1.AccName
FROM suppliers s1
WHERE s1.accref LIKE '^SC%'
AND COALESCE(
( SELECT i1.dateexpire
FROM InsuranceDetails i1
WHERE i1.accref = s1.accref ), '19000101' )
<= '20071130' ;

--
Anith