At 12:05 PM 9/09/2005 +0000, you wrote:

>Hi
>
>I have 3 tables, tblCustomer (self explanatory), tblCorrispondence
>(the different types of corrispondance sent to multiple customers ie
>emails, letter etc) and a link table tblCustCorr that contains data
>linking which corrispondence that has been sent to the which
>customers.
>
>I have been able to use a simple query to find customers that have
>not
>been sent any corrispondence:
>
>SELECT *
>FROM Tblcustomer c LEFT JOIN tblCustCorr sc ON c.custId = sc.custId
>WHERE sc.custId Is Null
>
>I now wish to modify the query to find the customers that have
>either
>recieved no corrispondence or have not recieved a particular
>letter/email etc.
>
>I thought this would require a nested select statement along the
>lines
>of:
>
>SELECT c.*, (SELECT CorrespondenceId, CorrespondenceDate,
>CorrespondenceType,from
>tblcorrespondence co LEFT JOIN tblCustcorr sc ON co.correspondenceid
>= sc. correspondenceid)
>FROM Tblcustomer c
>LEFT JOIN tblCustcorr sc ON c.CustId = sc.CustId
>WHERE sc.CustId Is Null or sc.CorrespondenceType <>"letter"
>
>But I seem to get errors whenever I use the '('
>
>Is this possible?

No. A subquery must return a scalar (a single value). You have a lot of
SQL syntax errors in there (anyway) but, if you want multiple columns from
a second table, you need a join. However, I can't see what use the field
list in the subquery is anyway, considering you want to know about records
that don't exist.

>Is there a better way?

Yes - if I understand rightly what you want....except I think
CorrespondenceType probably isn't an sc.column but a co.column....

SELECT c.* from Customer c
where (
(not exists (
select 1 from tblCustcor sc
join tblcorrespondence co
on co.correspondenceid = sc.correspondenceid
where sc.CustId = c.CustId
and co.CorrespondenceType = ? /* 'letter') single quotes!! */
and co.correspondenceid = ? ) )
or (not exists (
select 1 from tblCustcor sc1
where sc1.CustId = c.CustId) ) )

You have two NOT predicates here (and if one NOT predicate is slow then two
are slower!). However, if the first test returns true, the second one
won't run, so the swings and roundabouts of the short logic might make it
not too horrendous.

./heLen