Reply to Re: Select statement help

Your name:

Reply:


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

[Back to original message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация