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
Solved

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

Posted on 2014-09-03
4
121 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

809 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