Solved

4 tables, show the IDs in each table on one row

Posted on 2014-01-12
1
253 Views
Last Modified: 2014-01-16
I have 4 tables with 3 common fields

4 tables:
1.      WASP
2.      REGIONALS
3.      SCCM
4.      ACCOUNTS

3 common fields in all tables:
1.      Asset
2.      Office
3.      Owner


I need a SQL query to compare the 4 tables and list the 3 common fields

each row should have one asset value e.g.

example of output
0
Comment
Question by:JCTDD
1 Comment
 
LVL 8

Accepted Solution

by:
Surrano earned 500 total points
ID: 39775875
If I understand correctly you need a full join on Asset field and list the other two for each table.

select w.asset, w.office, w.name,
  r.asset, r.office, r.name,
  s.asset, s.office, s.name,
  a.asset, a.office, a.name
from wasp w
  full outer join regionals r on (coalesce(w.asset,r.asset)=r.asset)
  full outer join sccm s on (coalesce(w.asset,r.asset,s.asset)=s.asset)
  full outer join accounts a on (coalesce(w.asset,r.asset,s.asset,a.asset)=a.asset)
-- insert where clause here if needed
order by coalesce(w.asset,r.asset,s.asset,a.asset);

Open in new window


or natural join which means that all non-join common fields must be uniquely named, leaving only asset as common name:
select asset, wasp_office, wasp_name,
  reg_office, reg_name,
  sccm_office, sccm_name,
  acc_office, acc_name
from (select asset, office as wasp_office, name as wasp_name from wasp) w
  natural full outer join (select asset, office as reg_office, name as reg_name from regionals) r
  natural full outer join (select asset, office as sccm_office, name as sccm_name from sccm) s
  natural full outer join (select asset, office as acc_office, name as acc_name from accounts) a
-- insert where clause here if needed
order by asset;

Open in new window

0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question