V4RB ... SQL really

[Marcus Bointon]

on 10/4/03 17:00, Pedro fp at lists at pedro.net.au wrote:

> G'day Folks
> 
> Can someone please tell me what SQL where clause you would use to get
> all the records in table A that have *no* related records in table B?
> 
> Am I right in thinking that to get records that have a related record
> I'd use ...
> WHERE tableA.RecID = the_Ptr
> & if so could I use ...
> WHERE NOT tableA.RecID = the_Ptr
> got records with no related records?

Nope, that won't work (as you've probably found). The query that you really
want would be something like:

SELECT * FROM tableA WHERE RecID NOT IN (SELECT RecID from WHERE
tableA.RecID = the_Ptr)

But unfortunately you can't do subselects in Valentina yet. There are a few
tricks you can do by doing a LEFT JOIN and only selecting null results:

SELECT * FROM tableA LEFT JOIN tableB ON tableB.RecID = tableA.the_Ptr WHERE
tableB.RecID IS NULL

(these queries are not tested!)

Alternatively, do it the old fashioned way - use your host language to
iterate through what you've found and drop results you don't want.

Marcus
-- 
Marcus Bointon
Synchromedia Limited: Putting you in the picture

marcus at synchromedia.co.uk | http://www.synchromedia.co.uk