Thanks Cetin

This is what I want...... always use left join

>>Hi All
>>
>>I got 2 table ...
>>numtot (drawdate datetime,digit4 nchar(4),total Int)
>>result drawdate datetime,price1 nchar(4),price2 nchar(4),,,con_10 nchar(4)
>>
>>Is this the only way of selecting the value from numtot againt result
>>or there is a better way to do it
>>
>>

>>   select * from numtot where drawdate = @mdate and
>>   digit4 = (select price1 from result where drawdate = @mdate) or
>>   digit4 = (select price2 from result where drawdate = @mdate) or
>>   digit4 = (select price3 from result where drawdate = @mdate) or
>>   digit4 = (select spe_1 from result where drawdate = @mdate) or
>>   digit4 = (select spe_2 from result where drawdate = @mdate) or
>>   digit4 = (select spe_3 from result where drawdate = @mdate) or
>>   digit4 = (select spe_4 from result where drawdate = @mdate) or
>>   digit4 = (select spe_5 from result where drawdate = @mdate) or
>>   digit4 = (select spe_6 from result where drawdate = @mdate) or
>>   digit4 = (select spe_7 from result where drawdate = @mdate) or
>>   digit4 = (select spe_8 from result where drawdate = @mdate) or
>>   digit4 = (select spe_9 from result where drawdate = @mdate) or
>>   digit4 = (select spe_10 from result where drawdate = @mdate)or
>>   digit4 = (select con_1 from result where drawdate = @mdate) or
>>   digit4 = (select con_2 from result where drawdate = @mdate) or
>>   digit4 = (select con_3 from result where drawdate = @mdate) or
>>   digit4 = (select con_4 from result where drawdate = @mdate) or
>>   digit4 = (select con_5 from result where drawdate = @mdate) or
>>   digit4 = (select con_6 from result where drawdate = @mdate) or
>>   digit4 = (select con_7 from result where drawdate = @mdate) or
>>   digit4 = (select con_8 from result where drawdate = @mdate) or
>>   digit4 = (select con_9 from result where drawdate = @mdate) or
>>   digit4 = (select con_10 from result where drawdate = @mdate)
>>

>
>For VFP this would work if not there are too many drawings on a given drawdate :
>

>Local array arrResult[1]
>arrResult = .null.
>
>select price1,price2,price3,;
>	spe_1,spe_2,spe_3,spe_4,spe_5, ;
>	spe_6,spe_7,spe_8,spe_9,spe_10, ;
>	con_1,con_2,con_3,con_4,con_5,;
>	con_6,con_7,con_8,con_9,con_10 ;
>	from result where drawdate = m.mdate ;
>	into array arrResult
>select * from numtot ;
>	where drawdate = mdate and ;
>	Ascan(arrResult,digit4) > 0
>

>For SQL server (and/or VFP with slight change) you might write it like :
>

>select numtot.* from numtot
>  inner join result on numtot.drawdate = result.drawdate
>  where numtot.drawdate = @mdate and
>    (numtot.digit4 = result.price1 or
>     numtot.digit4 = result.price2 or
>     ...
>     numtot.digit4 = result.con10)
>

Cetin