TSQL - Create a JOIN, knowing that it may not exist, but show the initial records at least instead of nothing [bad idea] - abandoned it
-
That was hard to describe. Because of some bad business logic, or dealing with say vendors that retire or go out of business after 20 years, the question of what to do with them remains. I'm thinking it's best to ask for the vendor anyways, and join any task if it exists. If it doesn't exist, then at least give me the vendor info, and the rest can be blank. So if I can get the VendorID, CompanyName and Vendor DeleteFlag even if the JOIN can't JOIN or the JOIN request doesn't exists, that would solve some issues for me. I don't know what this is called, and the keywords I'm using don't bring up much except for NOT EXIST. I'm just looking for a point in the right direction or no it can't be done. SELECT vendor.Vendor_ID, vendor.Company_Name, vendor.DELETE_FLAG, proj_cost.cost, proj_cost.pref_vendor FROM vendor LEFT OUTER JOIN proj_cost ON proj_cost.Vend_ID = vendor.Vendor_ID WHERE proj_cost.proj_id = '4077' AND proj_cost.proj_stage = 'construction' AND proj_cost.vers_id = '8' AND proj_cost.task_Oper = '6' AND proj_cost.vend_id = '54' AND proj_cost.task_id = 'TK_EX_044'
If it ain't broke don't fix it Discover my world at jkirkerx.com
-
That was hard to describe. Because of some bad business logic, or dealing with say vendors that retire or go out of business after 20 years, the question of what to do with them remains. I'm thinking it's best to ask for the vendor anyways, and join any task if it exists. If it doesn't exist, then at least give me the vendor info, and the rest can be blank. So if I can get the VendorID, CompanyName and Vendor DeleteFlag even if the JOIN can't JOIN or the JOIN request doesn't exists, that would solve some issues for me. I don't know what this is called, and the keywords I'm using don't bring up much except for NOT EXIST. I'm just looking for a point in the right direction or no it can't be done. SELECT vendor.Vendor_ID, vendor.Company_Name, vendor.DELETE_FLAG, proj_cost.cost, proj_cost.pref_vendor FROM vendor LEFT OUTER JOIN proj_cost ON proj_cost.Vend_ID = vendor.Vendor_ID WHERE proj_cost.proj_id = '4077' AND proj_cost.proj_stage = 'construction' AND proj_cost.vers_id = '8' AND proj_cost.task_Oper = '6' AND proj_cost.vend_id = '54' AND proj_cost.task_id = 'TK_EX_044'
If it ain't broke don't fix it Discover my world at jkirkerx.com
But it would interesting to be able to do it.
If it ain't broke don't fix it Discover my world at jkirkerx.com
-
That was hard to describe. Because of some bad business logic, or dealing with say vendors that retire or go out of business after 20 years, the question of what to do with them remains. I'm thinking it's best to ask for the vendor anyways, and join any task if it exists. If it doesn't exist, then at least give me the vendor info, and the rest can be blank. So if I can get the VendorID, CompanyName and Vendor DeleteFlag even if the JOIN can't JOIN or the JOIN request doesn't exists, that would solve some issues for me. I don't know what this is called, and the keywords I'm using don't bring up much except for NOT EXIST. I'm just looking for a point in the right direction or no it can't be done. SELECT vendor.Vendor_ID, vendor.Company_Name, vendor.DELETE_FLAG, proj_cost.cost, proj_cost.pref_vendor FROM vendor LEFT OUTER JOIN proj_cost ON proj_cost.Vend_ID = vendor.Vendor_ID WHERE proj_cost.proj_id = '4077' AND proj_cost.proj_stage = 'construction' AND proj_cost.vers_id = '8' AND proj_cost.task_Oper = '6' AND proj_cost.vend_id = '54' AND proj_cost.task_id = 'TK_EX_044'
If it ain't broke don't fix it Discover my world at jkirkerx.com
Not sure if this is quite what you are looking for, but here goes:
SELECT
vendor.Vendor_ID,
vendor.Company_Name,
vendor.DELETE_FLAG,
proj_cost.cost,
proj_cost.pref_vendor
FROM vendor
LEFT OUTER JOIN proj_cost ON proj_cost.Vend_ID = vendor.Vendor_ID
WHERE (proj_cost.proj_id = '4077'
AND proj_cost.proj_stage = 'construction'
AND proj_cost.vers_id = '8'
AND proj_cost.task_Oper = '6'
AND proj_cost.vend_id = '54'
AND proj_cost.task_id = 'TK_EX_044')
-- vendor without matching proj_cost
OR proj_cost.Vend_ID IS NULL -
Not sure if this is quite what you are looking for, but here goes:
SELECT
vendor.Vendor_ID,
vendor.Company_Name,
vendor.DELETE_FLAG,
proj_cost.cost,
proj_cost.pref_vendor
FROM vendor
LEFT OUTER JOIN proj_cost ON proj_cost.Vend_ID = vendor.Vendor_ID
WHERE (proj_cost.proj_id = '4077'
AND proj_cost.proj_stage = 'construction'
AND proj_cost.vers_id = '8'
AND proj_cost.task_Oper = '6'
AND proj_cost.vend_id = '54'
AND proj_cost.task_id = 'TK_EX_044')
-- vendor without matching proj_cost
OR proj_cost.Vend_ID IS NULLThat works perfect actually! I did not think of the OR clause to get a null join. This opens up some new possibilities for me to solve some business logic in this program I'm writing, and you made me a better coder today as well. I looked at the logic closer, and I already had the vendors in an object that I passed to this DB function, so I just looped the object and filled in the blanks if it existed and that solved my problem on the cost analysis report. But I have a huge business logic factory or class that can use this type of help when building a construction project. Thanks for the lesson!
If it ain't broke don't fix it Discover my world at jkirkerx.com
-
That was hard to describe. Because of some bad business logic, or dealing with say vendors that retire or go out of business after 20 years, the question of what to do with them remains. I'm thinking it's best to ask for the vendor anyways, and join any task if it exists. If it doesn't exist, then at least give me the vendor info, and the rest can be blank. So if I can get the VendorID, CompanyName and Vendor DeleteFlag even if the JOIN can't JOIN or the JOIN request doesn't exists, that would solve some issues for me. I don't know what this is called, and the keywords I'm using don't bring up much except for NOT EXIST. I'm just looking for a point in the right direction or no it can't be done. SELECT vendor.Vendor_ID, vendor.Company_Name, vendor.DELETE_FLAG, proj_cost.cost, proj_cost.pref_vendor FROM vendor LEFT OUTER JOIN proj_cost ON proj_cost.Vend_ID = vendor.Vendor_ID WHERE proj_cost.proj_id = '4077' AND proj_cost.proj_stage = 'construction' AND proj_cost.vers_id = '8' AND proj_cost.task_Oper = '6' AND proj_cost.vend_id = '54' AND proj_cost.task_id = 'TK_EX_044'
If it ain't broke don't fix it Discover my world at jkirkerx.com
The problem is that the
WHEREclause is filtering out records where theproj_costcolumns areNull. The cleanest solution is to move those filters to the join itself:SELECT
vendor.Vendor_ID,
vendor.Company_Name,
vendor.DELETE_FLAG,
proj_cost.cost,
proj_cost.pref_vendor
FROM vendor
LEFT OUTER JOIN proj_cost
ON proj_cost.Vend_ID = vendor.Vendor_ID
AND proj_cost.proj_id = '4077'
AND proj_cost.proj_stage = 'construction'
AND proj_cost.vers_id = '8'
AND proj_cost.task_Oper = '6'
AND proj_cost.vend_id = '54'
AND proj_cost.task_id = 'TK_EX_044'Alternatively, use a sub-query or common table expression[^]:
WITH cte As
(
SELECT
Vend_ID,
cost,
pref_vendor
FROM
proj_cost
WHERE proj_id = '4077'
AND proj_stage = 'construction'
AND vers_id = '8'
AND task_Oper = '6'
AND vend_id = '54'
AND task_id = 'TK_EX_044'
)
SELECT
vendor.Vendor_ID,
vendor.Company_Name,
vendor.DELETE_FLAG,
cte.cost,
cte.pref_vendor
FROM vendor
LEFT OUTER JOIN cte ON cte.Vend_ID = vendor.Vendor_IDBoth approaches work by applying the filters to the
proj_costtable before the join adds back any records from thevendortable which don't have a matching record.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
The problem is that the
WHEREclause is filtering out records where theproj_costcolumns areNull. The cleanest solution is to move those filters to the join itself:SELECT
vendor.Vendor_ID,
vendor.Company_Name,
vendor.DELETE_FLAG,
proj_cost.cost,
proj_cost.pref_vendor
FROM vendor
LEFT OUTER JOIN proj_cost
ON proj_cost.Vend_ID = vendor.Vendor_ID
AND proj_cost.proj_id = '4077'
AND proj_cost.proj_stage = 'construction'
AND proj_cost.vers_id = '8'
AND proj_cost.task_Oper = '6'
AND proj_cost.vend_id = '54'
AND proj_cost.task_id = 'TK_EX_044'Alternatively, use a sub-query or common table expression[^]:
WITH cte As
(
SELECT
Vend_ID,
cost,
pref_vendor
FROM
proj_cost
WHERE proj_id = '4077'
AND proj_stage = 'construction'
AND vers_id = '8'
AND task_Oper = '6'
AND vend_id = '54'
AND task_id = 'TK_EX_044'
)
SELECT
vendor.Vendor_ID,
vendor.Company_Name,
vendor.DELETE_FLAG,
cte.cost,
cte.pref_vendor
FROM vendor
LEFT OUTER JOIN cte ON cte.Vend_ID = vendor.Vendor_IDBoth approaches work by applying the filters to the
proj_costtable before the join adds back any records from thevendortable which don't have a matching record.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
That's pretty clever. my mind was no where near thinking of it like that. And it runs pretty quick as well, and gives me exactly what I want with null records on a failed join. This opens up new possibilities for me with some of the business logic. On a side note, I'm surprised that I didn't get laughed at for wanting to craft something like this. I couldn't think of a better way to handle this without writing a bunch of extra functions that might not be fast and reliable. Thanks Richard!
If it ain't broke don't fix it Discover my world at jkirkerx.com
