• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 151
  • 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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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