I have a SQL query that shows details of jobs. here I have check if null value found with inner join then pass with precondition.
Here is my SQL query :
SELECT
Jobs.EmailsSent,
Jobs.SubscriberCount,
Jobs.CompletedOn,
Jobs.PreparedEmailID,
Jobs.JobID,
Jobs.CreatedOn,
tbl_Categories.CategoryName,
tbl_Email_master.Title,
tbl_From_master.Name As FromUsername,
tbl_User_master.Name AS CreatedBy
FROM
Jobs
INNER JOIN
tbl_Email_master ON Jobs.PreparedEmailID = tbl_Email_master.Id
INNER JOIN
tbl_From_master ON Jobs.FromuserID = tbl_From_master.Id
AND tbl_Email_master.FromUser = tbl_From_master.Id
INNER JOIN
tbl_Categories ON tbl_Categories.Id = Jobs.CategoryID
OR (tbl_Categories.Id IS NOT NULL
AND Jobs.CategoryID IS NULL)
-- here pass default value like ALL if Jobs.CategoryID found NULL
INNER JOIN
tbl_User_master ON Jobs.UserID = tbl_User_master.Id
AND tbl_Email_master.user_id = tbl_User_master.Id
AND tbl_From_master.user_id = tbl_User_master.Id
AND tbl_Categories.user_id = tbl_User_master.Id
WHERE
Jobs.JobID = '7'
Can we do with this?
--------------------------------updated--------------------------------
with this ans :
INNER JOIN tbl_Categories ON tbl_Categories.Id = coalesce(Jobs.CategoryID,tbl_Categories.Id) OR (tbl_Categories.Id IS NOT NULL AND coalesce(Jobs.CategoryID,tbl_Categories.Id)=tbl_Categories.Id)
i have retrieving row like :
now I just only want raws/raw with default category value ALL.
--------------------------------as text----------------------------------
CATEGORY NAME
0 44 NULL Friends
0 44 NULL Family Relatives
0 44 NULL Business Clients
0 44 NULL Corporate Profiles
0 44 NULL test
0 44 NULL Infisms Clients
0 44 NULL Infisms Clients
here i just want to return row/rows with Category Name as ALL.
Just use
coalesce()
functionassuming
tbl_Categories.Id
is anon-null
column