Solved

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

Posted on 2014-09-03
4
123 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 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

726 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