suggestion for a (dbx-)database-problem

[Thomas Pfister]

Hi all,

I need a general suggestion for the following problem:

table1
Right explaination
1 area 1
2 area 2
3 area 3


table 2
user right
jon 1,3
jim 2,3
jeff 1

table 3

info right
300 1
200 3
300 2
400 1


-> I make an application with dbx4 and some different databases and the
sql-statements are stored readable
in different folders (for each database) and I know the user and want to
make sure that Jon only the the
rows with the rithgs from table2....

select * from table3 where ... (and right in (select right from table 2
where user = jon) order by info

What is the best way to realize this ?

I can insert another where-part after read the sql-file and before insert in
the sqldataset, but I look for a more elegant
solution <g>.

the admin / change of the sql-statements (for the different database or
changed columns) should make a db-admin and I don't will store the
sql-statements in the database or in a crypted file....


thanks for any ideas!!



;-) thomas

[Bill Todd [TeamB]]

Post by Thomas Pfister
select * from table3 where ... (and right in (select right from table
2 where user = jon) order by info

This will not work because the value returned from table 2 will be 1,3
and this does not match any value in the right column of table 3.

Post by Thomas Pfister
What is the best way to realize this ?

You need to normalize your database so that you do not store multiple
values in a single field as you are doing with the right column in
table 2. Either table 2 should contain:

jon 1
jon 3

or you need a detail table for table2 to store the rights for jon.

[Thomas Pfister]

Bill,
sorry; I think I haven't not written clearly enough

In table2 I have for Jon 2 rows and so on and the subselect is correct "and
rights in (1,3)" and works if I make it hardcoded.

The database isn't my database and I can't insert any tables or change the
tables....


:-) thomas

Post by Bill Todd [TeamB]

Post by Thomas Pfister
select * from table3 where ... (and right in (select right from table
2 where user = jon) order by info

This will not work because the value returned from table 2 will be 1,3
and this does not match any value in the right column of table 3.

Post by Thomas Pfister
What is the best way to realize this ?

You need to normalize your database so that you do not store multiple
values in a single field as you are doing with the right column in
jon 1
jon 3
or you need a detail table for table2 to store the rights for jon.
--
Bill Todd (TeamB)

[Bill Todd [TeamB]]

Can you not store

select * from table3 where ... (and right in (select right from table 2
where user = :username) order by info

and assign the parameter value at runtime?