how to select rows that not meet a join condition

[bmeza]

hi, how can i do to select those invoices that their numbers parts have rate "tlcan" and dont have a valid "tlcan" certificate of origin for the invoice date

select claused have 3 tables and a just want those rows that not meet the join condition

here its my code

select invoice, date, part, line from
certorig inner join impmov on part=co_part inner join impfact on invoice=invoicem
and date between co_datei and co_datef and rate='TLCAN'
and co_trat='TLCAN'

 

[bborissov]

[COLOR=blue]select[/color] invoice, [COLOR=blue]date[/color], part, line
       [COLOR=blue]from[/color] certorig
       [COLOR=blue]inner[/color] [COLOR=blue]join[/color] impmov [COLOR=blue]on[/color] part=co_part
       [COLOR=#FF00FF]LEFT[/color] [COLOR=blue]join[/color] impfact [COLOR=blue]on[/color] invoice=invoicem                    and
                             [COLOR=blue]date[/color] between co_datei and co_datef and
                             rate    = [COLOR=red]'TLCAN'[/color]                  and
                             co_trat = [COLOR=red]'TLCAN'[/color]
[COLOR=blue]WHERE[/color] impfact.invoicem [COLOR=blue]IS[/color] NULL

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP