• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 146
  • Last Modified:

How do i join two tables using a column with some null values

Offers table
OfferNo      clientID      caseID      
1      10      null
2      11      110
3      12      null
4      12      120
5      12      null

Enquiry table
ID      enquiryType      caseID
1      A      100
2      B      101
3      C      102
4      D      103            
5      E      120

EnquiryType table
EnquiryType            Description
A            enquiry type a
B            enquiry type b
C            enquiry type c
D            enquiry type d
E            enquiry type e

Using Microsift SQL Server T-SQL how do I select and return all rows from the offers table above. If a caseid exists in the offers table I want to extract the description from the enquiryType table into my result set by joining across the enquiry table otherwise just have null in that column ie I want to return the result set below

Offerno      clientid       caseid      description
1      10      null      null
2      11      110      null      
3      12      null      null
4      12      120      enquiry type e  
5      12      null      null
1 Solution
An outer join should work in your case:

SELECT O.OfferNo, O.ClientID, O.CaseID, ET.EnquiryType
   FROM Offers O LEFT JOIN (Enquiry E INNER JOIN EnquireType ET
                                      ON ET.EnquiryType = E.EnquiryType)
       ON E.CaseID = O.CaseID

Note that I put the join between the Enquiry and EnquiryType inside parenthesis. If you don't do that you would have to use an OUTER JOIN to link Enquiry to EnquiryType.


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now