--- In firebird-support@yahoogroups.com, "Fabio Gomes" wrote:

> Hi i have doubts again ^^
>
> I tried this:
>
> SELECT C.cli_nome
> FROM clientes C
> WHERE NOT EXISTS (
> SELECT V.vnd_cod
> FROM vendas V, clientes C
> WHERE V.cli_cod=C.cli_cod AND V.vnd_data BETWEEN '2006-03-01' AND
> '2006-03-31' AND C.ved_cod='2'
> )
> AND C.ved_cod='2'
>
> I tought it worked on the first time, but it just give me results if
> i dont have any records on the vendas table, and what i want is all
> the clients (clientes) that doesnt have any records in the vendas
> table.
>
> This query above just give me results if i dont have ANY records on
> the vendas table.
>
> Is there a way to fix it?

Sure, your query is incorrect! NEVER define the same alias twice in
the same query (you could use C2 for the second clientes alias, but in
your case you're better off just removing it altogether)!!!

Your simplified query should probably just be:

SELECT C.cli_nome
FROM clientes C
WHERE NOT EXISTS (
SELECT V.vnd_cod
FROM vendas V
WHERE V.cli_cod=C.cli_cod
AND V.vnd_data BETWEEN '2006-03-01' AND '2006-03-31'
)
AND C.ved_cod='2'

HTH,
Set