help for SQL-command 1

[Nifrabar]

Hello to all

In table 'AlleVrijwilligers'
I do have a field 'VwWerkgroe'C(40)which is combined from (max)10 several combobox-values. i.e. "P14 Z12 W12"
Not all records are having a content like that.

There is a look-up table which holds all comboboxvalues i.e
xCode C(3):
P14
Z12
W12

I try to select all records from table 'AlleVrijwilligers' which have within field VwWerkgroe one or more items from field xCode of code-table.

I expected code down below will do so but it throws error 1801 'Error correlating fields'
Any suggestions about what is wrong?

CLOSE TABLES all
use allevrijwilligers in 0
USE code IN 0

SELECT * FROM allevrijwilligers as X1 where;
 exists (SELECT * FROM code as X2 WHERE (x2.xCode $ x1.vwwerkgroe));
 INTO CURSOR crsResult
 
 SELECT crsResult
 BROWSE

 

[cbasoz]

SELECT distinct x1.* ;
 FROM allevrijwilligers as X1 ;
 INNER JOIN code X2 ;
 on x1.vwwerkgroe like '%'+x2.xCode+'%'

PS: IMHO it would be better to redesign this table to have those values from lookup in multiple rows.

Cetin Basoz
MS Foxpro MVP, MCP

 

[Nifrabar]

Cetin,

Thansk for reply which as far as I can see exactly does what I'm looking for.
I fully agree that the database-design is bad. But I import these tables from 3rd party commercial application which I can't redesign.

Star for you.

-Bart