Brighten Godfrey wrote:
>
> Hi,
>
> I'm pretty new to mySQL so please excuse me if this is something I should
> know, but I've looked in the documentation and haven't seen any way to do
> this; joining tables doesn't seem to do quite what I want it to. Here's
> what I want to do:
>
> I need to have multiple people editing a database, each with their own
> accounts, each editing their own part of the database. So each user
> could add rows to a table and delete only their own rows. I thought that
> the best way to do this would be to have one table per user, all with the
> same columns:
>
> Jane's table:
>
> House color Square footage Address
> ---------------------------------------------------
> Red 2400 123 Watson St.
> Blue 3356 453 Mill St.
> Green 1000 1940 Lake Ave.
>
> Bruce's table:
>
> House color Square footage Address
> ---------------------------------------------------
> Red 1300 723 Woodside Ave.
> White 1900 9000 Prairie Rd.
> Red 2150 728 Woodside Ave.
>
> So only Jane can add and delete rows from Jane's table, and only Bruce can
> add and delete from Bruce's table. Now, the snag comes here: I want to be
> able to search across all the tables, so, that, for example, I could find
> all the red houses of >= 2000 sq. ft., ordered by square footage, and it
> would return this:
>
> House color Square footage Address
> ---------------------------------------------------
> Red 2150 728 Woodside Ave.
> Red 2400 123 Watson St.
>
> Is there any way I can search across multiple tables like this?
> Preferably I would be able to search across multiple tables without
> specifying each individual table name, e.g. selecting from all tables
> whose name starts with "House_".
>
> Alternately, could I store all the info in one table, and somehow restrict
> access based on rows?
>
> Thanks,
> Brighten Godfrey
Hi Brighten
MySQL doesn't provide row based priviledges.
You only can restrict the access priviledges on the client side.
The problem with this is, that a person, not using your client, can break your security
(perhaps this isn't a problem in your case).
What you search for is called UNION.
But MySQL doesn't provide this command (yet).
So you have to either use multiple SELECTs and sort on the client side, or you have to use
a temporary table to store partial results.
You also could make one table with all info in it for searching, and several identical
tables for editing.
But then you have to handle the updates from the editable tables to the searchable one
internally.
In SQL it is forbidden to use wildcards in database-, table- and/or column names.
So you always have to give the full name, not only to MySQL.
Tschau
Christian
PS: Sorry for the late answer, I was on vacation.
