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

>
> Anyway, sorry about all those newbie questions, but i have another

one ^^

>
> Now i need to make a report that shows all the clients that didnt buy
> anything on that month.. so i tried this:
>
> SELECT C.cli_nome
> FROM clientes C, vendas V
> WHERE (SELECT V.vnd_cod FROM vendas V, clientes C WHERE

V.cli_cod=C.cli_cod)

> IS NULL AND V.vnd_data BETWEEN '2006-1-01' AND '2006-1-31'
>
> I dont really understand how subselects work so i just guessed it,

and it

> seens that i ve guessed it wrong, cause it didnt work.

Close. You have a few choices.

Since you seem to be starting out, I recommend you change the way you
are joining tables. You seem to be using the old SQL 89 syntax for joins.

A join should look like this:

SELECT C.cli_nome
FROM clientes C
JOIN vendas V on (V.cli_cod=C.cli_cod)
WHERE V.vnd_data BETWEEN '2006-1-01' AND '2006-1-31'

(that is not the solution to your problem, but you can see it is more
readable because you have separated out the conditions of the results
from the conditions that are only there to link the tables of the joins).

In this case you do not need a join at all (although you could use a
left join and where right field is not null in the where clause for
the same result).

But your solution is going to want to select the records from the
clientes table where no matching record is in the vendas table with a
particular condition.

Using Exists:

select *
from clientes C
where not exists (
select *
from vendas v
where V.cli_cod=C.cli_cod
and V.vnd_data BETWEEN '2006-1-01' AND '2006-1-31'
)

Using IN:

select *
from clientes C
where c.cli_cod not in (
select v.cli_cod
from vendas v
where V.vnd_data BETWEEN '2006-1-01' AND '2006-1-31'
)

Using Left Join:

select *
from clientes C
left join vendas v on (V.cli_cod=C.cli_cod)
where V.cli_cod IS NOT NULL
and V.vnd_data BETWEEN '2006-1-01' AND '2006-1-31';

In older versions of Firebird and Interbase, the IN operator would
have been slower, but 1.5 is pretty good, it will internally convert
it to an exists test where possible. So you should choose the query
that generates the best plan.

If more than one choose the same best plan, then use the one that is
most human understandable.

Also be aware that if vnd_data is a timestamp field then you are going
to miss the final day of data. If it is a date field then leave it as
is. If you are using timestamp, change the condition to:

V.vnd_data >= '2006-1-01' AND V.vnd_data < '2006-2-1'

Or transactions starting after 31 Jan 2006 12:00 am will be missed.

Adam