V4RB ... SQL really

[Ruslan Zasukhin]

on 4/10/03 6:39 PM, Marcus Bointon at marcus at synchromedia.co.uk 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:

Correct.

In fact I wonder that such simple question "IS NOT LINKED" should be made in
such complex way in standard SQL. Also I think any DBMS will be able do
above query in the best possible way.

I think for Valentina must be invented something special.

> SELECT * FROM tableA LEFT JOIN tableB ON tableB.RecID = tableA.the_Ptr WHERE
> tableB.RecID IS NULL
> 
> (these queries are not tested!)

Just not correct syntax for Valentina's left join.

> 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.

-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://listserv.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------