how to create a self-join

how to create a self-join

Post by Ken » Thu, 03 Oct 2002 16:56:47



I have a flat database of business contacts. Below is some sample
data:

ID, Name, Address, Country
1, John Smith, XXX, US
2, Bob Smith, XXX, UK
3, Sean Smith, XXX, US
4, Jane Smith, XXX, France

I want to create a portal on the left side of the layout which shows
the names of the contacts based on a self-join. On the right side, it
will be the record of the contacts.

How do I create the self-join?

Ken

 
 
 

how to create a self-join

Post by Marc-AndrĂ© Paiemen » Thu, 03 Oct 2002 18:54:21



Quote:> I have a flat database of business contacts. Below is some sample
> data:

> ID, Name, Address, Country
> 1, John Smith, XXX, US
> 2, Bob Smith, XXX, UK
> 3, Sean Smith, XXX, US
> 4, Jane Smith, XXX, France

> I want to create a portal on the left side of the layout which shows
> the names of the contacts based on a self-join. On the right side, it
> will be the record of the contacts.

> How do I create the self-join?

> Ken

Hello,

In that case, since you want to see the list of all contacts no matter which
record you're currently viewing, you will need a "constant" field (auto
entered data = 1) as the match field for your self-join (both sides of the
relationship). Then in the portal, apart from names, you can add a button to
go to the related record. This button uses the same relationship, something
that I don't really understand... but hey it works :)

Once there are more contacts than visible rows in the portal, you will find
that the selected row may not always be visible, that is, after selecting a
row, the portal will redraw to show the rows from the top. So it is not
always clear which line you're editing. Bridget Eley posted a solution to
that problem a few monts ago. Look in the Google archive for her post titled
"Re: How do you highlight the selected Record".

Good luck !

Marc-Andr Paiement
Montreal

 
 
 

1. How to create a self join in English Query domain editor

I would like to create a relationship, such as, "authors co-authors with
authors", or, "employees reports to managers", or, "managers have subordinates",
which is essentially a self join.  The authors table would have an additional
coauthorID field that joins to the primary key in authors; or, the employee
table would have a ManagerID field that joins to the primary key.

When I try to add the same entity twice into the relationship and then add the
Verb phrasing, and click Apply, I get the error: "The same entity is listed more
than once in this relationship. This is only valid if it has a different join
path each time it is used."  However, there really is only one join path that
can be specified, e.g., authors.au_id = authors.co_au_id, or, employees.empID =
employees.manager_empID.  If I edit one of the instances of the entity in the
relationship and specify the join path, it allows me to Apply without error; but
then when I try to test application, I get the error "This form contained an
error" and this relationship is open in the Relationship Editor.

If it is possible to specify this kind of self-join relationship, how do I do
it?

Ron E. Woods
Product Development Consultant
HRSoft, LLC

2. Running VFP 6.0 EXE problem

3. HELP: Creating value list with calculation/self-join???

4. Need Source Code With Barcoding

5. Self join VS equi join

6. Using the WHERE clause when SP param is not null

7. Inner Join / Self Joins help!

8. I want to configure my SQL environment

9. Self Join vs Join between different tables

10. self-join

11. Self Left Outer Join

12. Self-join

13. Self JOIN query (I think)