Solved

How to join these two data sources to produce the output shown?

Posted on 2014-09-03
4
117 Views
Last Modified: 2014-09-03
examplejoin.pngHi there, given the above image, what SQL command would join the desired output.  All my join experiments failed to return all data or returned nulls.  I thought about using isnull but in some cases it was the key column that was null and I wasn't sure if this was the right approach.

Thank you!


SQL Server 2000 answer required.
0
Comment
Question by:dgloveruk
  • 2
4 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 40302223
FULL OUTER JOIN

SELECT
      ISNULL(t1.customerID, t2.Customer) AS CustomerID
    , Cars
    , Houses
    , Boats
    , Planes
FROM Table1 t1
      FULL OUTER JOIN Table2 t2
                  ON t1.CustomerID = t2.Customer

Open in new window

0
 
LVL 4

Assisted Solution

by:Randy Knight, MCM
Randy Knight, MCM earned 250 total points
ID: 40302265
Here is one solution.

SELECT a.CustomerID, MAX(a.Cars) AS Cars, MAX(a.Houses) AS Houses, MAX(a.Boats) AS Boats, MAX(a.Planes) AS Planes
FROM 
(

 SELECT t1.CustomerID, t1.Cars, t1.Houses, 0 AS Boats, 0 AS Planes
--INTO #tmp
 FROM t1 
	LEFT JOIN t2  ON t1.CustomerID = t2.CustomerID
 UNION ALL 
 SELECT t2.CustomerID, 0, 0, t2.Boats, t2.Planes
 FROM t2 
	LEFT JOIN t1 ON t2.CustomerID = t1.CustomerID
) a
GROUP BY a.CustomerID

Open in new window

0
 

Author Closing Comment

by:dgloveruk
ID: 40302291
Thanks PortletPaul, Randy,
Whilst Paul your solution looked elegant it wasn't quite what was needed , nulls came back instead of 0's which I think I could get around by using isnull.  Randys solution worked great.  Paul spotted the deliberate mistake on my primary key name joining customer on customerid in his example :)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40302396
>>"nulls came back instead of 0's "
...

so use ISNULL()

    , Cars

    , ISNULL(Cars,0)
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Help - MSSQL - Averages 5 27
Find results from sql within a time span 11 33
SQL Query assistance 16 27
Proper Case SQL Command 2 13
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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

822 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