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