|
|
Posted by krusz10 on 09/21/07 10:57
On 20 Sep, 22:31, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (krus...@hotmail.co.uk) writes:
> > I have a table (tableA) that lists a number of other tables on the
> > same database. I want to search each table listed in tableA for an
> > existence of an known entry and then display that entry and the table
> > its in
> > I can do it in other scripting languages but I'm new to SQL. Can you
> > help
>
> > I sort of want to do the following
> > Select * from everything_in(select tablename from tableA) where entry
> >= 'fred'
>
> Sounds like you have a bad database design. Normally, each table should
> describe a unique entity, why a query like your would not be meaningful
> in most cases. (The one exception I can think of is auditing columns that
> could appear in all tables.)
>
> The query to write is:
>
> WITH all_tables AS (
> SELECT ...
> FROM tblA
> UNION ALL
> SELECT ...
> FROM tblB
> ...
> )
> SELECT ... FROM all_tables WHERE col = 'fred'
>
> If you want to work from yor table of table names, you would need to
> generate the query dynamically.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
----------------------------------------------------------------------------------------------------------------------------------------------
Thanks for the prompt reply. Not sure it would help unless I could
probably use variables in some way.
The database is a back end DB to a Microsoft system (SMS). There are a
number of collection tables that perform different tasks against there
contents. Listed in the DB are unique objects that are linked by a
primary key across the tables. These unique objects can exist in any
number of the tables depending on what task is required.
What I was hoping to get is a list of tables (tasks) that a unique
object is listed in, and the list of tables I want to search in (which
change daily) are found in tblA.
Hope this makes things clearer.
Can it be done in a single query?
In simple terms a script would look something like this:
Foreach $tblname in ('select tablename from tblA')
do
If true (Select objectname from $tblname where objectname = 'FRED')
Print $tblname
Navigation:
[Reply to this message]
|