List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:July 20 1999 8:39pm
Subject:Re: Searching across multiple tables
View as plain text  
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.

Thread
DISTINCT keyword not doing as I expect...DC Mahoney15 Jul
  • Re: DISTINCT keyword not doing as I expect...Paul DuBois15 Jul
    • Re: DISTINCT keyword not doing as I expect...jkraai15 Jul
  • Optimizing a P133 for MySQLVan15 Jul
    • Re: Optimizing a P133 for MySQLSasha Pachev15 Jul
    • Re: Optimizing a P133 for MySQLJunster15 Jul
      • Re: Optimizing a P133 for MySQLOrlando Andico15 Jul
        • Re: Optimizing a P133 for MySQLJunster15 Jul
          • Re: Optimizing a P133 for MySQLOrlando Andico15 Jul
            • Re: Optimizing a P133 for MySQLEd Carp15 Jul
              • Re: Optimizing a P133 for MySQLOrlando Andico15 Jul
                • Re: Optimizing a P133 for MySQLJon Drukman15 Jul
                  • Re: Optimizing a P133 for MySQLOrlando Andico15 Jul
                • Re: Optimizing a P133 for MySQLChris Trown15 Jul
                  • Re: Optimizing a P133 for MySQLEd Carp15 Jul
                    • Re: Optimizing a P133 for MySQLOrlando Andico16 Jul
                      • Re: Optimizing a P133 for MySQLVan16 Jul
              • Re: Optimizing a P133 for MySQLVan15 Jul
              • Re: Optimizing a P133 for MySQLT├Ánu Samuel16 Jul
                • Re: Optimizing a P133 for MySQLJon Drukman16 Jul
        • Re: Optimizing a P133 for MySQLJon Drukman15 Jul
      • Searching across multiple tablesBrighten Godfrey15 Jul
        • Searching across multiple tablessinisa15 Jul
        • Re: Searching across multiple tablesChristian Mack21 Jul
    • Re: Optimizing a P133 for MySQL {Interim Results}Van16 Jul
      • Re: Optimizing a P133 for MySQL {Interim Results}Ed Carp16 Jul
  • DISTINCT keyword not doing as I expect...Michael Widenius30 Jul
Re: Optimizing a P133 for MySQLDerick H Siddoway16 Jul
Re: Optimizing a P133 for MySQLKen Farwell16 Jul