Skip to content
Advertisement

If null in one table select from Other

I have 2 tables inquiry and Application table I want to build a view that contains the user information based on data from these two tables.

Example

I Have Table Inquiry

FirstName, LastName, Address, email

I have table Application

FirstName, LastName, Address, email

Am querying the tables using the email field, This is what i want,

SELECT FirstName From InquiryTBL where email = @Email If Null Select FirstName From ApplictionTBL where email = @email

This is kinda what I have been trying

 SELECT 
      CASE
      WHEN a.Email = null
  THEN (SELECT FirstName from dbo.Inquiry_Tbl where email = @Email)
  ELSE a.FirstName
  END As [FirstName],  

Advertisement

Answer

If email is in both tables, you can JOIN on that field and then use COALESCE to pull non-null data:

SELECT 
    Email = COALESCE( i.Email, a.Email)
    , FirstName = COALESCE(i.FirstName, a.FirstName)
    , LastName = COALESCE(i.LastName, a.LastNamej)
FROM InquiryTBL i
    LEFT JOIN ApplicationTBL a
        ON i.Email = a.Email
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement