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

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
JCTDDAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
SurranoConnect With a Mentor System EngineerCommented:
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
All Courses

From novice to tech pro — start learning today.