?
Solved

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

Posted on 2014-01-12
1
Medium Priority
?
262 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 8

Accepted Solution

by:
Surrano earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

770 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