• 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
0
jo_0210
Asked:
jo_0210
1 Solution
 
mrihmCommented:
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.

HTH
0

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