[V4RB] Need a bit of help with Many to Many relationship

[Ruslan Zasukhin]

on 2/6/03 6:31 PM, Kevin Windham at kevo at gatorgraphics.com wrote:

> I am working on an app in which I have two tables related to each other
> by a third table which only has 2 object pointer fields. It is
> basically the same as the Has table in example 4 of the V4RB tutorial.
> 
> I am trying to wrap my brain around how to create the proper queries
> for these tables.
> 
> In the tutorial there is this example.
> 
> Now you can try a SQL query on both tables:
> SELECT *
> FROM Person, Has, Task
> WHERE byte_fld < 10 AND status = 1 AND
> person.id = has.person_ptr AND has.task_ptr = task.id
> ORDER BY string_fld DESC
> 
> Again, with Valentina you can skip the specification of a LINK
> condition:
> SELECT *
> FROM Person, Has, Task
> WHERE byte_fld < 10 AND status = 1
> ORDER BY string_fld DESC
> 
> I have two basic questions that I can't seem to figure out.
> 
> 1. If I simply want to get a list of all the RecIDs in one table
> related to a specific record in the other table, how would I write that
> query, and can it be written without the link conditions somehow or is
> that not possible in this case.

    SELECT T1.RecID
    FROM T1, T12, T2
    WHERE T2.RecID = 548

Here 548 -- is example value of that specific record.
 
But in fact, FOR THIS TASK you can do this in simpler way.
The middle table has all needed info for this.

    SELECT DISTINCT T12.ptr1
    FROM T12
    WHERE T12.ptr2 = 548


> 2. Somewhat related to the first question, how does Valentina know what
> the link conditions are? Is it from the location of the fields in the
> where clause? Also, when is it appropriate to leave off the table
> qualifier in an SQL expression. In the above to queries, I would
> normally write task.status instead of just status.

You can skip link condition ONLY for ObjectPtr - link
So Valentina simply do link by t1.RecID and t2.Object to table1.

In future version, when will be added support for standard RBD-link,
FOREIGN KEY, I do not see problems to allow skip link conditions for RDB
link also.

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