Foxpro vs Paradox SQL

[Rob Grant]

I'm building a system with a data model of Parent table with the FileNumber, etc, and child table  with each record having a different item and associated value.

The requirement is to search on the child table for item1 having a value, and item2 having a value, and item3 having a value, etc., returning the parent table records.

A Foxpro collegue developed the following SQL structure which works in FoxPro, but I can't get anything like it to work in Paradox8.  PFW8 just gives invalid token and argument errors.  I've tried using the query builder, without success.

SELECT *  FROM parent ;
   WHERE parent.parentkey IN (SELECT DISTINCT parentkey ;
        FROM  child WHERE child.type=type1 and child.value = value1);
    AND  parent.parentkey IN (SELECT DISTINCT parentkey  ;
        FROM  child WHERE child.type=type2 and child.value = value2);
    AND  parent.parentkey IN (SELECT DISTINCT parentkey ;
        FROM  child WHERE child.type=type3 and child.value = value3)
 

I'd hate to let FoxPro win this one, but I also need to figure this query out.  Can anyone suggest a Paradox-compatible SQl statement to accomplish this task?

Thanks
Rob Grant
 

[Mike Irwin]

A somewhat yucky query, as written. You certainly don't need all the
semi-colons, and the word "value" is reserved in SQL92 (sorry, but FoxPro,
like Access's SQL, is out of line here). It resolves to

which looks like

SELECT
*
FROM
parent
WHERE
parent.parentkey IN (
SELECT DISTINCT
parentkey
FROM
child
WHERE

child.type = child.value)

but you'll have to rename the "value" field. IMHO, you're luck that "type"
isn't reserved too, and I'd rename _that_ field too, while you have the
opportunity.

Hope this helps

Mike