Joining two tables and dealing with null values

Question

Joining two tables and dealing with null values

mattech06

SSCrazy

Points: 2484
More actions
May 28, 2014 at 6:51 am

#291740

Hi,
I would like to create a dataset from two tables, similar to
tableA.name, tableA.job, tableB.salary, tableB.startDate
tableA has an id column Aid and table b has an id column Bid.
I'd like to be able to join the tables so that if there is not a match between Aid and Bid then name and job detail is displayed but salary and startDate have a default message eg 'No Data' and if there is a match then all four columns have data in them.
eg
name job salary startdate
------ ---- ------ ----------
bob dd 99.0 1/2/2004 (has matching id)
tim ee ND ND (no matching id)
thanks

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Check BOL (the SQL Server help system) for the ISNULL() function, and have a look at the related functions COALESCE() and NULLIF() too.

Note that your output columns can only be of one datatype - you can't mix and match, so if 'ND' is your replacement for NULL values, you will have to CAST non-null values to a character datatype.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mattech06 SSCrazy Points: 2484 More actions · Answer 2

can you use those functions in the FROM section?

This is the working vers, not my sample from above and includes a ref table as well and there's a bit more too it.

SELECT

b.TransDate, b.TransType, b.SortCode, b.AccNum, b.TransDesc,

(ISNULL(b.Debit, 0) + ISNULL (b.Credit, 0)) as Amount,

ISNULL(r.OrthoID, 999) as OrthoID

ISNULL(t.PatientID, 0) as PatientID

FROM

EPSBankTransactions b

LEFT JOIN EPSBankRef r on SUBSTRING(b.TransDesc, 0, 26) = r.BankID

LEFT JOIN EPSTransactions t on r.OrthoID = t.PatientID

so when I DON'T include the sql relating to EPSTransactions t table the result row count is correct (278) and any null values in the r.OrthoID column display 999. Cool. But when I try and join the EPSTransactions table it only returns the rows where the r.OrthoID matches the t.PatientID (11)

What I'd like is to have (278) rows and where there isn't a match, t.PatientID has 0 in it.

thanks,

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

mattech06 (5/28/2014)
can you use those functions in the FROM section?
This is the working vers, not my sample from above and includes a ref table as well and there's a bit more too it.
SELECT
b.TransDate, b.TransType, b.SortCode, b.AccNum, b.TransDesc,
(ISNULL(b.Debit, 0) + ISNULL (b.Credit, 0)) as Amount,
ISNULL(r.OrthoID, 999) as OrthoID
ISNULL(t.PatientID, 0) as PatientID
FROM
EPSBankTransactions b
LEFT JOIN EPSBankRef r on SUBSTRING(b.TransDesc, 0, 26) = r.BankID
LEFT JOIN EPSTransactions t on r.OrthoID = t.PatientID
so when I DON'T include the sql relating to EPSTransactions t table the result row count is correct (278) and any null values in the r.OrthoID column display 999. Cool. But when I try and join the EPSTransactions table it only returns the rows where the r.OrthoID matches the t.PatientID (11)
What I'd like is to have (278) rows and where there isn't a match, t.PatientID has 0 in it.
thanks,

I suspect you didn't post the entire query here. Do you also have a where clause on this query? Does it have any predicates referencing t?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mattech06 SSCrazy Points: 2484 More actions · Answer 4

Hi, yes!

SELECT

b.TransDate, b.TransType, b.SortCode, b.AccNum, b.TransDesc,

(ISNULL(b.Debit, 0) + ISNULL (b.Credit, 0)) as Amount,

ISNULL(r.OrthoID, 999) as OrthoID

ISNULL(t.PatientID, 0) as PatientID

FROM

EPSBankTransactions b

LEFT JOIN EPSBankRef r on SUBSTRING(b.TransDesc, 0, 26) = r.BankID

LEFT JOIN EPSTransactions t on r.OrthoID = t.PatientID

WHERE

t.LedgerType in (26,30)

AND t.TransactionDate >= '2014-04-01 00:00.00' AND t.TransactionDate <= '2014-04-30 23:00.00'

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Have you tried the following?

Change the left join here

LEFT JOIN EPSTransactions t on r.OrthoID = t.PatientID

to this

RIGHT JOIN EPSTransactions t on r.OrthoID = t.PatientID

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

mattech06 (5/28/2014)
Hi, yes!
SELECT
b.TransDate, b.TransType, b.SortCode, b.AccNum, b.TransDesc,
(ISNULL(b.Debit, 0) + ISNULL (b.Credit, 0)) as Amount,
ISNULL(r.OrthoID, 999) as OrthoID
ISNULL(t.PatientID, 0) as PatientID
FROM
EPSBankTransactions b
LEFT JOIN EPSBankRef r on SUBSTRING(b.TransDesc, 0, 26) = r.BankID
LEFT JOIN EPSTransactions t on r.OrthoID = t.PatientID
WHERE
t.LedgerType in (26,30)
AND t.TransactionDate >= '2014-04-01 00:00.00' AND t.TransactionDate <= '2014-04-30 23:00.00'

Much as I suspected. This effectively turns your left join into an inner join because it will only return those rows where there is a match. 😉

Moving those conditions to the join should work. Try this and let us know if this is what you were looking for.

SELECT

b.TransDate, b.TransType, b.SortCode, b.AccNum, b.TransDesc,

(ISNULL(b.Debit, 0) + ISNULL (b.Credit, 0)) as Amount,

ISNULL(r.OrthoID, 999) as OrthoID,

ISNULL(t.PatientID, 0) as PatientID

FROM

EPSBankTransactions b

LEFT JOIN EPSBankRef r on SUBSTRING(b.TransDesc, 0, 26) = r.BankID

LEFT JOIN EPSTransactions t on r.OrthoID = t.PatientID

AND t.LedgerType in (26,30)

AND t.TransactionDate >= '2014-04-01 00:00.00' AND t.TransactionDate <= '2014-04-30 23:00.00'

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

I'm not sure if it will work the same way. If r.BankID will always be 26 characters long this should be equivalent and SARGable. If not, there's a risk for wrong information returned.

SELECT

b.TransDate, b.TransType, b.SortCode, b.AccNum, b.TransDesc,

(ISNULL(b.Debit, 0) + ISNULL (b.Credit, 0)) as Amount,

ISNULL(r.OrthoID, 999) as OrthoID,

ISNULL(t.PatientID, 0) as PatientID

FROM

EPSBankTransactions b

LEFT JOIN EPSBankRef r on r.BankID LIKE b.TransDesc

LEFT JOIN EPSTransactions t on r.OrthoID = t.PatientID

AND t.LedgerType in (26,30)

AND t.TransactionDate >= '2014-04-01 00:00.00' AND t.TransactionDate <= '2014-04-30 23:00.00'

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mattech06 SSCrazy Points: 2484 More actions · Answer 8

Thanks Sean, that did the trick...give yourself a 1000 points!

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

mattech06 (5/30/2014)
Thanks Sean, that did the trick...give yourself a 1000 points!

Glad that worked for you and thanks for letting us know.

(the points don't really help, I tried to sell them on ebay but nobody was interested. ;-))

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Well, at least it is working.

I find it odd that nobody was interested in Sean's points on ebay. Seems very uneducated or crass of people - sheesh.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

SQLRNNR (5/30/2014)
Well, at least it is working.
I find it odd that nobody was interested in Sean's points on ebay. Seems very uneducated or crass of people - sheesh.

I know!!! I am shocked. The same place where people spent thousands of dollars to buy a stale grilled cheese sandwich because the burn marks sort of look like a certain religious person...

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Sean Lange (5/30/2014)
SQLRNNR (5/30/2014)
Well, at least it is working.
I find it odd that nobody was interested in Sean's points on ebay. Seems very uneducated or crass of people - sheesh.
I know!!! I am shocked. The same place where people spent thousands of dollars to buy a stale grilled cheese sandwich because the burn marks sort of look like a certain religious person...

:w00t::hehe::-D

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events