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

Posted on 2007-10-02
Last Modified: 2010-03-20
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
Question by:jo_0210
    1 Comment
    LVL 5

    Accepted 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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now