SubQuery Error 2

[DaveL]

Can someone please point out where I am going wrong in this basic SQL statement? In FoxPro 6.0, I am trying to write a simple query program. When I run the following SQL, I am getting an error that reads "SQL: Subquery is invalid".

select *;
from cont_det;
where cont_det.contract in (select * from trackit)

 

[marsh]

In the subquery, you must specify the field name:

If the field in the trackit table is contractnum then:

select *;
from cont_det;
where cont_det.contract in (select contractnum from trackit)

 

[jonscott8]

Hi DaveL,

Using the IN operator, your subquery can have only one output column, and I presume the table trackit has more than one field.

select * ;
from cont_det ;
where cont_det.contract in (select trackit.contract from trackit)

Jon Hawkins

The World Is Headed For Mutiny,
When All We Want Is Unity. - Creed

 

[DaveL]

Those suggestions did the trick. Thanks for the help!!!

 

[krisb]

Your sub-select table does not have to have only ONE field in it, but you DO have to specify WHICH field you want to select as Marsh explained.

Thanks!