invalid subquery vfp6

[fanlinux90]

i use vfp 6 should i display the vendors name as a subquery of the purchase table

select date, value, (select vendor.name from vendor inner join purchase on vendor.type = purchase.vendtype) from purchase where name = "queen"

but I get the error invalid subquery

 

[Chris Miller]

VFP does not support this type of subquery as field.

Chriss

 

[Mike Lewis]

In fact, as far as I remember, the only subquery supported by VFP 6.0 is in the WHERE clause. (Someone will correct me if I'm wrong about that.)

In tnis case, you should be able to get the desired result with a simple JOIN.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Wouldn't it be much easier to do this query instead?

select date, value, vendor.name from purchase inner join vendor on purchase.vendtype=vendor.type and vendor.name = "queen"

I mean, it's still qute useless to make that join by vendortype and name, isn't it? A pruduct you purchase should have it's ver spefic unique single vendor, not just any vendor of such products.

If you do this, technically, it will work, if there is a vendor of the purchase type that is named "queen", but then you also know in advane that the vend.nme will be "queen", the join just confirms it exists and is the right vendor type.



Chriss

 

[Chris Miller]

By the way, as a little quiz: What will be in the vendor.name column of the result, if no vendor.type=ourhase.vendtype with that name exists?

Spoiler:

Spoiler

Caution, this actually is a trick question

Chriss

 

[fanlinux90]

sorry for my writing error.
Can you do two inner joins with dbfs tables?

 

[Mike Lewis]

Can you do two inner joins with dbfs tables?

Most certainly. With VFP 6.0, you can join up to nine tables. In VFP 9.0, there is no limit.

In all versions, joins can either be nested, like this:

FROM <table>;
   JOIN <table>; ;
      JOIN <table>;
         JOIN <table> ;
           ON <condition> ;
        ON <condition> ;
     ON <condition>

or they can be sequential:

FROM <table>;
   JOIN <table>;
     ON <condition>;
   JOIN <table>;
     ON <condition>;
   JOIN <table>;
     ON <condition>

Each has its pros and cons. Personally I always use the second format, but not everyone agrees.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

What you did there wasn't failing because of the join, it was failing as a subquery within the result fieldlist. Even in T-SQL or other SQL dialects queries that are used in place of a field are rarely a good solution and are very restricted, as they not only have to have at maximum one result row, they also must only have one result field.

If you pick a single field of a single row, that's much easier to get from a table you have to join anyway.

Chriss