Hi Pete!

Yes, what you want is absolutely possible, but you have to improve
your SQL quite a bit.

First, subselects:
They can only return one field and must return a maximum of one row -
your SQL violates both.

Secondly, LEFT JOIN should only be used where an inner join is not
appropriate, used inappropriately they complicates the life of your
friend 'the optimizer'.

Thirdly, you should use single and not double quotes (double quotes
means something different).

I don't quite figure what you want, but my guess is that it is best
obtained by using NOT EXISTS, e.g.

SELECT <whatever>
FROM Tblcustomer c
WHERE NOT EXISTS(
SELECT * FROM tblCustCorr sc join tblcorrespondence co
on sc.correspondenceid = co.correspondenceid and
where sc.custId = c.custId and
sc.CorrespondenceType <> 'letter')

This basically lists everybody that has not received 'letter'. This of
course includes those that haven't received anything at all.

This should help,
Set

--- In firebird-support@yahoogroups.com, "petesouthwest" 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? Is there a better way?
>
> Many thanks for any help given
>
> Pete