>Hi,
>
>How can I filter records city-wise through the following select statement:
>
>
>Select simain.sino, ;
> simain.invdate, ;
> simain.pcode, ;
> simain.location, ;
> sisub.icode, ;
> sisub.qntymaj, ;
> sisub.qntymin, ;
> sisub.ratemaj, ;
> sisub.ratemin ;
> FROM simain, sisub ;
> WHERE simain.sino = sisub.sino ;
> Into Cursor tmpsi ;
> ORDER By simain.sino Readwrite
>
>Alter Table tmpsi Add Column City c(52) Null
>
>
>
>And, I have a table name location which has the following fields:
>
>
>1. pcode c(8)
>2. Location c(52)
>3. City c(52)
>4. country c(52)
>
>
>
>How can I join location with tmpSi based on pcode. Please note that one pcode (Customer code) can consist of multiple cities and if I want to filter the sales record with city like City='Dubai'. How can I do so? Please reply!


You don't need to join tmpsi with location afterwards
Try

Select simain.sino, ;
	simain.invdate, ;
	simain.pcode, ;
	simain.location, ;
	location.City, ;
	sisub.icode, ;
	sisub.qntymaj, ;
	sisub.qntymin, ;
	sisub.ratemaj, ;
	sisub.ratemin ;
	FROM simain, sisub ;
                   join location  on ( location.pcode = simain.pcode ) ;
	WHERE simain.sino = sisub.sino ;
	   and ( location.City = 'Dubai') ;
	Into Cursor tmpsi Readwrite ;
	ORDER By simain.sino