one to one relation

[auke]

Hi,

I've got two tables (both with a primary key) which are related
through a one-to-one relation (for security reasons). Both tables
store information about people. The information in the first table
should be accessible for everyone, while the information in the second
table should only be accessible to a select group of users. So the
setup is something like this:

Table 1 Table 2
id | Name | Adres | etc | |id | bankaccount | etc.

I turned referential integrity on. However, when I try to turn on
"Cascade update related fields", I get an error: Invalid field
definition "Id" in definition of index or relationship. What am I
doing wrong here?

(*) With referential integrity on I can't add a person in the second
table, without adding him/her in the first. That's good!

However, when I add a name to the first table, but do not fill in the
one of colums of the second table (eg bankacount), and I run a select
query on the people stored in the first table, this newly created
person does not show up in the select query. How can this be solved?

Of course I can circumvent this problem by demanding that at least one
of the colums of the second table is filled in? Is it possible to
achieve something opposite to (*) without giving op (*) itself?

[Stewart Allen]

With your select query include both table1 and table2 and do a left join
from table1 to table2
SELECT Table1.ID, Table2.BankAcco unt
FROM Table1 LEFT JOIN Table1.ID = Table2.ID;

This will show all the people in table even if the ID hasn't been entered
into table2. If you only want to display the people whose bankaccount
numbers are not entered, add to the above SQL statement by first removing
the ";"
WHERE Table2.BankAcco unt Is Null;

Stewart
"auke" <a.***********@ its.tudelft.nl> wrote in message
news:4d******** *************** ***@posting.goo gle.com...

Hi,

I've got two tables (both with a primary key) which are related
through a one-to-one relation (for security reasons). Both tables
store information about people. The information in the first table
should be accessible for everyone, while the information in the second
table should only be accessible to a select group of users. So the
setup is something like this:

Table 1 Table 2
id | Name | Adres | etc | |id | bankaccount | etc.

I turned referential integrity on. However, when I try to turn on
"Cascade update related fields", I get an error: Invalid field
definition "Id" in definition of index or relationship. What am I
doing wrong here?

(*) With referential integrity on I can't add a person in the second
table, without adding him/her in the first. That's good!

However, when I add a name to the first table, but do not fill in the
one of colums of the second table (eg bankacount), and I run a select
query on the people stored in the first table, this newly created
person does not show up in the select query. How can this be solved?

Of course I can circumvent this problem by demanding that at least one
of the colums of the second table is filled in? Is it possible to
achieve something opposite to (*) without giving op (*) itself?

[Auke Bergsma]

Thanks,

I set only the DEFAULT value. But this setting does not effect joins
created earlier (stupid me). So I had to go to relations and edit the
relation again!

That did the trick!!

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!