Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Select JOIN table 2 23
TSQL - IF ELSE? 3 29
create insert script based on records in a table 4 15
MS SQL Pivot table help 4 14
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

895 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now