CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Chris A
Chris A

Reputation: 95

How to handle NULL values in multiple SQL table joins

I have a SQL query that pulls data from four tables but if there's no data in the third table (SC_FeatureCart) then the query returns no data.

I understand that columns containing NULL do not match any values when creating the join, therefore they're excluded from the result set but I still want to be able to return values from the first two tables, SC_NumberCart and SC_Packages so can anyone help suggest how I can achieve this???

My SQL query is:

SELECT SC_NumberCart.Number, SC_Packages.PkgName, SC_Features.FeatureName  
FROM SC_NumberCart 
JOIN SC_Packages ON SC_NumberCart.PkgID = SC_Packages.PkgID 
JOIN SC_FeatureCart ON SC_NumberCart.NumberID = SC_FeatureCart.NumberID 
AND SC_NumberCart.RecordID = SC_FeatureCart.RecordID 
JOIN SC_Features 
ON SC_FeatureCart.FeatureID = SC_Features.FeatureID 
WHERE SC_NumberCart.CpyID = @CpyID

I can see and display the result using this SQL query if there's data sat in the SC_FeatureCart table as illustrated below.

<table id="t2">
      <tr>
        <th>Number</th>
        <th>PkgName</th>
        <th>FeatureName</th>
      </tr>
      <tr>
        <td>01157710125</td>
        <td>01 / 02 Medium (Landline)</td>
        <td>Call Whisper</td>
      </tr>
    </table>

Many thanks,

Chris

Upvotes: 0

Views: 686

Answers (2)

zimdanen
zimdanen

Reputation: 5626

Use a LEFT JOIN to denote that you want results from the left table even if there's no match in the right table.

SELECT SC_NumberCart.Number, SC_Packages.PkgName, SC_Features.FeatureName  
FROM SC_NumberCart 
LEFT JOIN SC_Packages ON SC_NumberCart.PkgID = SC_Packages.PkgID 
LEFT JOIN SC_FeatureCart ON SC_NumberCart.NumberID = SC_FeatureCart.NumberID 
                            AND SC_NumberCart.RecordID = SC_FeatureCart.RecordID 
LEFT JOIN SC_Features ON SC_FeatureCart.FeatureID = SC_Features.FeatureID 
WHERE SC_NumberCart.CpyID = @CpyID

Here's a good explanation of the different types of joins.

Upvotes: 1

Abhijith Nagarajan
Abhijith Nagarajan

Reputation: 4030

Use outer joins instead of inner join

Upvotes: 1

Related Questions