Posted by Erland Sommarskog on 04/26/06 00:17

(av1@email.t-com.hr) writes:
> Question still remains: how to SELECT similar data of interest across
> many tables?
>
> What would be the syntax to include additional column data representing
> the "system" in a combined table ?

SELECT system = 'system of tbl1', col1 ,col2, col2,
FROM tbl1
WHERE ...
UNION ALL
SELECT 'system2, col1, col2, col3,
FROM tbl2
WHERE ...
UNION ALL

If the WHERE clause is generally applicable, you can do:

SELECT system, co1l, col2, col3, ...
FROM (SELECT system = 'system of tbl1', col1 ,col2, col2,
FROM tbl1
UNION ALL
SELECT 'system2, col1, col2, col3,
FROM tbl2
UNION ALL
...) AS x
WHERE ...

Here I'm using a derived table, which logically a temp table within
the query, but not necessarily comnputed as such. That is the optimizer
may recast the computation order for better effeciency.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Simple JOIN, INTERSECT query
• Prev in forum: Simple JOIN, INTERSECT query
• Thread view: Re: SELECT * FROM "multiple_tables" WHERE "field_(in_those_tables)_have_the_same_paricular_value" ?

[Reply to this message]