Click here to Skip to main content
15,868,016 members
Home / Discussions / Database
   

Database

 
AnswerRe: TSQL - JOIN cancels out my date filter Pin
Graham Breach27-Nov-22 21:38
Graham Breach27-Nov-22 21:38 
GeneralRe: TSQL - JOIN cancels out my date filter Pin
jkirkerx28-Nov-22 6:32
professionaljkirkerx28-Nov-22 6:32 
GeneralRe: TSQL - JOIN cancels out my date filter Pin
jschell28-Nov-22 10:44
jschell28-Nov-22 10:44 
QuestionTSQL - Create a JOIN, knowing that it may not exist, but show the initial records at least instead of nothing [bad idea] - abandoned it Pin
jkirkerx21-Nov-22 9:15
professionaljkirkerx21-Nov-22 9:15 
AnswerMaybe this is a bad idea, so I abandoned it. Did a work around instead. Pin
jkirkerx21-Nov-22 9:35
professionaljkirkerx21-Nov-22 9:35 
AnswerRe: TSQL - Create a JOIN, knowing that it may not exist, but show the initial records at least instead of nothing [bad idea] - abandoned it Pin
Graham Breach21-Nov-22 10:29
Graham Breach21-Nov-22 10:29 
GeneralRe: TSQL - Create a JOIN, knowing that it may not exist, but show the initial records at least instead of nothing [solved] - May run with this Pin
jkirkerx21-Nov-22 11:04
professionaljkirkerx21-Nov-22 11:04 
AnswerRe: TSQL - Create a JOIN, knowing that it may not exist, but show the initial records at least instead of nothing [bad idea] - abandoned it Pin
Richard Deeming21-Nov-22 22:02
mveRichard Deeming21-Nov-22 22:02 
The problem is that the WHERE clause is filtering out records where the proj_cost columns are Null.

The cleanest solution is to move those filters to the join itself:
SQL
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[^]:
SQL
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_ID

Both approaches work by applying the filters to the proj_cost table before the join adds back any records from the vendor table 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

GeneralRe: TSQL - Create a JOIN, knowing that it may not exist, but show the initial records at least instead of nothing [bad idea] - abandoned it Pin
jkirkerx22-Nov-22 6:58
professionaljkirkerx22-Nov-22 6:58 
QuestionOrder by several static string values, error "Conversion failed when converting the varchar value 'FX' to data type int" Pin
jkirkerx14-Nov-22 10:44
professionaljkirkerx14-Nov-22 10:44 
AnswerRe: Order by several static string values, error "Conversion failed when converting the varchar value 'FX' to data type int" [sort of solved] Pin
jkirkerx14-Nov-22 11:29
professionaljkirkerx14-Nov-22 11:29 
GeneralRe: Order by several static string values, error "Conversion failed when converting the varchar value 'FX' to data type int" [sort of solved] Pin
jsc4222-Nov-22 10:31
professionaljsc4222-Nov-22 10:31 
GeneralRe: Order by several static string values, error "Conversion failed when converting the varchar value 'FX' to data type int" [sort of solved] Pin
jkirkerx22-Nov-22 10:50
professionaljkirkerx22-Nov-22 10:50 
GeneralRe: Order by several static string values, error "Conversion failed when converting the varchar value 'FX' to data type int" [sort of solved] Pin
jsc4222-Nov-22 23:26
professionaljsc4222-Nov-22 23:26 
GeneralRe: Order by several static string values, error "Conversion failed when converting the varchar value 'FX' to data type int" [sort of solved] Pin
jkirkerx23-Nov-22 6:11
professionaljkirkerx23-Nov-22 6:11 
QuestionCan any body share the schema database for security and share. Pin
Nguyễn Tuấn Anh 9216-Nov-22 5:40
Nguyễn Tuấn Anh 9216-Nov-22 5:40 
AnswerRe: Can any body share the schema database for security and share. Pin
Sam Hobbs6-Nov-22 10:05
Sam Hobbs6-Nov-22 10:05 
AnswerRe: Can any body share the schema database for security and share. Pin
jschell14-Nov-22 6:49
jschell14-Nov-22 6:49 
QuestionHow to avoid duplicate records while using a UNION - SOLVED Pin
Richard Andrew x6428-Oct-22 15:50
professionalRichard Andrew x6428-Oct-22 15:50 
AnswerRe: How to avoid duplicate records while using a UNION - SOLVED Pin
Richard Deeming30-Oct-22 23:01
mveRichard Deeming30-Oct-22 23:01 
GeneralRe: How to avoid duplicate records while using a UNION - SOLVED Pin
Richard Andrew x6431-Oct-22 1:13
professionalRichard Andrew x6431-Oct-22 1:13 
AnswerRe: How to avoid duplicate records while using a UNION - SOLVED Pin
Andrea Simonassi2-Nov-22 19:55
Andrea Simonassi2-Nov-22 19:55 
AnswerRe: How to avoid duplicate records while using a UNION - SOLVED Pin
PIEBALDconsult6-Nov-22 5:54
mvePIEBALDconsult6-Nov-22 5:54 
GeneralRe: How to avoid duplicate records while using a UNION - SOLVED Pin
Richard Andrew x646-Nov-22 6:56
professionalRichard Andrew x646-Nov-22 6:56 
QuestionAzure Synapse Where myString like '%Pattern1%Pattern2%' Pin
j11codep3-Oct-22 15:02
j11codep3-Oct-22 15:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.