not entirely sure what results you are actually looking for, but....

Looks like you could use test3 as a linking table between test1 and test2, plus add criteria for the match between test1 and test2 (since there are values in test3 that don't synch with test1/test2..) e.g.

Select t1.*, t3.getthisval ;
	from test1 t1 ;
	join test3 t3 On t1.cprod=t3.cprod ;
	join test2 t2 On t3.cwhse=t2.cwhse And t2.nwhse=t1.nwhse ;
	order By t1.nwhse, t1.cprod ;
	into Cursor curesults

>Hi all,
>Considering these test tables, can I get all the columns of TEST1 and TEST3.GetThisVal column using a single select-sql? Also, the query must be using the JOIN clause as opposed to the WHERE clause.
>

>close databases all
>set safety off
>set confirm off
>create table test1 (nwhse i, cprod c(1))
>insert into test1 values (1,'A')
>insert into test1 values (1,'C')
>insert into test1 values (2,'B')
>insert into test1 values (2,'C')
>insert into test1 values (3,'B')
>insert into test1 values (3,'A')
>insert into test1 values (3,'D')
>
>create table test2 (nwhse i, cwhse c(2))
>insert into test2 values (1,'W1')
>insert into test2 values (2,'W2')
>insert into test2 values (3,'W3')
>
>create table test3 (cwhse c(2), cprod c(1), getthisval i)
>insert into test3 values ('W1','A',1)
>insert into test3 values ('W1','B',2)
>insert into test3 values ('W1','C',3)
>insert into test3 values ('W2','A',11)
>insert into test3 values ('W2','B',22)
>insert into test3 values ('W3','A',111)
>insert into test3 values ('W3','D',222)
>

>TIA