Re: Tricky SQL problem - retrieve information_schema info and make use of it.

On Mon, Mar 21, 2016 at 3:48 PM, Paul Linehan <linehanp(at)tcd(dot)ie> wrote:

> Hi all,
>
>
> I'll explain the problem and then I'll give you a schema you should
> be able to cut and paste.
>
> I have a system which has different tables for each salesman (please
> don't blame me for this snafu). **Same** table structure for each
> person.
>
> I want to be able to query results for each salesman - but new sales
> personnel are being added and deleted all the time, so a static list
> is not appropriate.
>

OK, we won't blame you. But since the tables are identical, have you
considered inheritance?

Create table allsalespersons... to have a master table with the same
structure as the individual salesperson.

For existing tables, convert them to child tables:
alter table fred inherit allsalespersons;
...

For new tables just create them as inherited to begin with.

Now you can select from "allsalespersons" and get everyone.

Cheers,
Steve