Solved

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

Posted on 2014-09-03
4
125 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
[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
  • 2
4 Comments
 
LVL 49

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 49

Expert Comment

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

so use ISNULL()

    , Cars

    , ISNULL(Cars,0)
0

Featured Post

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

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

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…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

696 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