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
x
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