data from multiple tables

Hi
I have 6 databases and each has a table sys_items. My client asked me
to create a list of all the unique items from these tables from all
the databases.
select a. item, b, item, c.item, d.item, e.item, f.item
from a, b, c, d, e,f
there are a few common items in each table. so it means i want all the
common and unique items in my list.
any idea' suggestions'
please let me know
thanks
Ajit"Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
news:1184705626.221486.182570@.e16g2000pri.googlegroups.com...
> Hi
> I have 6 databases and each has a table sys_items. My client asked me
> to create a list of all the unique items from these tables from all
> the databases.
> select a. item, b, item, c.item, d.item, e.item, f.item
> from a, b, c, d, e,f
> there are a few common items in each table. so it means i want all the
> common and unique items in my list.
>
SELECT item FROM a.dbo.tbl UNION
SELECT item FROM b.dbo.tbl UNION
SELECT item FROM c.dbo.tbl UNION
SELECT item FROM d.dbo.tbl UNION
SELECT item FROM e.dbo.tbl UNION
SELECT item FROM f.dbo.tbl ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Try:
select
a.item
from
DatabaseA.dbo.sys_items a
join DatabaseB.dbo.sys_items b on b.Item = a.Item
join DatabaseC.dbo.sys_items c on c.Item = a.Item
join DatabaseD.dbo.sys_items d on d.Item = a.Item
join DatabaseE.dbo.sys_items e on e.Item = a.Item
join DatabaseF.dbo.sys_items f on f.Item = a.Item
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
news:1184705626.221486.182570@.e16g2000pri.googlegroups.com...
Hi
I have 6 databases and each has a table sys_items. My client asked me
to create a list of all the unique items from these tables from all
the databases.
select a. item, b, item, c.item, d.item, e.item, f.item
from a, b, c, d, e,f
there are a few common items in each table. so it means i want all the
common and unique items in my list.
any idea' suggestions'
please let me know
thanks
Ajit|||I think he wants to find the items that are common to all DB's.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:GKednS7PK8RAsADbnZ2dnUVZ8sKlnZ2d@.gi
ganews.com...
"Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
news:1184705626.221486.182570@.e16g2000pri.googlegroups.com...
> Hi
> I have 6 databases and each has a table sys_items. My client asked me
> to create a list of all the unique items from these tables from all
> the databases.
> select a. item, b, item, c.item, d.item, e.item, f.item
> from a, b, c, d, e,f
> there are a few common items in each table. so it means i want all the
> common and unique items in my list.
>
SELECT item FROM a.dbo.tbl UNION
SELECT item FROM b.dbo.tbl UNION
SELECT item FROM c.dbo.tbl UNION
SELECT item FROM d.dbo.tbl UNION
SELECT item FROM e.dbo.tbl UNION
SELECT item FROM f.dbo.tbl ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks guys.. I tried Union and it worked...