CODEWITHSUNDEEP
sqlsql-server
user3376642
user3376642

Reputation: 527

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],

Upvotes: 3

Views: 1137

Answers (2)

Shawn
Shawn

Reputation: 4786

If the email is in both tables or in only one table, use a FULL OUTER JOIN on email inside a sub-select. Then you can filter that sub-select by the email address you need.

DECLARE @email varchar(20) = '[email protected]' ;

SELECT s1.FirsTName, s1.LastName, s1.Email
FROM (
  SELECT COALESCE(i.FirstName, a.FirstName) AS FirstName
    , COALESCE(i.LastName, a.LastName) AS LastName
    , COALESCE(i.email, a.email) AS email
  FROM InquiryTBL i
  FULL OUTER JOIN ApplicationTBL a ON i.email = a.email
) s1
WHERE s1.Email = @email
;

https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=62d0f0a1026150de6b4fd9be3d058dbb

NOTE: To pick the first non-null value, you can use either coalesce() or isnull(). coalesce() is ANSI-compliant, can take more than 2 arguments and can be used in most databases. isnull() is primarily a Microsoft T-SQL function and takes only 2 arguments. It can be quicker in some cases than coalesce(), but it's not as portable. Both will also pick the first non-null value in your selected order, so you can change which value you want first.

Upvotes: 0

Russell Fox
Russell Fox

Reputation: 5435

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

Upvotes: 3

Related Questions