MSSQL Query Not Returning Null Columns/Results

Question

I'm kind of new to SQL queries so this is probably something easily fixed, but I can't seem to find any answers.

I've got a bunch of related tables. E.g. SPClients contains iClientID and sName, SPDocTypes contains iDocTypeID and sName, SPCreditor contains iCreditorID and sName

and then

SPDocIndex contains iIndexID, and then foreign keys to iClientID, iDocTypeID and iCreditorID listed above.

If I do a simple SELECT * FROM SPDocIndex I get all results, with just the IDs being displayed, which isn't much use when bound to a datagrid. So I use an inner join so the actual names appear rather than just their IDs, like so:

SELECT * FROM SPDocIndex
INNER JOIN SPClients ON SPDocIndex.iClientID=SPClients.iClientID
INNER JOIN SPDocType ON SPDocIndex.iDocTypeID=SPDocType.iDocTypeID
INNER JOIN SPCreditor ON SPDocIndex.iCreditorID=SPCreditor.iCreditorID

And the query "works" but it only returns rows that have data in all three columns. If the iCreditorID column of SPDocIndex is null, then that row is NOT returned...but I would like all rows to be returned irrespective of whether the columns are null or not.

add isnull(SPDocIndex.iCreditorID,'')=isnull(SPCreditor.iCreditorID,'') and check if works — Ven, Jul 05 '17 at 12:38
Possible duplicate of [SQL Inner Join On Null Values](https://stackoverflow.com/questions/2202172/sql-inner-join-on-null-values) — mjwills, Jul 05 '17 at 12:40
@jarlh - thank you. That did the trick! I don't know what the difference is between LEFT, RIGHT, INNER etc (clearly...) — Benny O'Neill, Jul 05 '17 at 12:42
@BennyO'Neill : read this to learn more on joins. http://www.studytonight.com/dbms/joining-in-sql.php — Prabhat G, Jul 05 '17 at 12:56
Possible duplicate of [SQL JOIN and different types of JOINs](https://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins) — Tab Alleman, Jul 05 '17 at 13:12

score 1 · Answer 1 · answered Jul 05 '17 at 13:28

Benny - some of the others responded in the comments, that you'll need to adjust your join to a left join instead of an inner join; indeed, that is the case here. Please check out this link for a quick tutorial on the differences between SQL joins.
https://www.w3schools.com/sql/sql_join.asp

score 0 · Answer 2 · answered Jul 05 '17 at 21:01

Inner Join will always return the return the matching records within two table. In order  to get the all the records from first table and matching record of second table you must use left join as shown below. Please try below and let me know if you have any further issues.

SELECT * FROM SPDocIndex
LEFT JOIN SPClients ON SPDocIndex.iClientID=SPClients.iClientID
LEFT JOIN SPDocType ON SPDocIndex.iDocTypeID=SPDocType.iDocTypeID
LEFT JOIN SPCreditor ON SPDocIndex.iCreditorID=SPCreditor.iCreditorID

MSSQL Query Not Returning Null Columns/Results

2 Answers2