Change second select to (select ref as second_ref from table2) as Peter alluded to.
::a
PS Sorry that\'s the first time I\'ve been able to help someone... and I\'m chuffed.
-----Original Message-----
From: profox-bounces .at. leafe .D.OT com [mailto:profox-bounces@leafe.com] On Behalf Of Peter Cushing
Sent: 30 November 2006 16:45
To: profox .at. leafe .D.OT com
Subject: Re: SELECT-Sql IN clause help
Alan Bourke wrote:
> I know this is simple but I\'m having a thick day.
>
> Two tables
>
> table1 (ref C(16))
>
> table2 (parent C(16), ref C(16))
>
> What\'s the VFP9 SELECT command to get the records out of table1 that do
> not have a matching record in table2, matching the ref fields.
>
> This works to get the ones that DO:
>
> select * from table1 where ref in (select ref from table2)
>
> but the opposite just returns one record, i.e.
>
> select * from table1 where ref not in (select ref from table2)
>
Can\'t see anything wrong with that Alan. I\'ve been using that syntax
for years and not noticed a problem. Maybe if they had different names
(E.g. ref1, ref2) it might make a difference.
Peter
[excessive quoting removed by server]
©2006 Adam Buckland |
