SQL JOIN (Result of join is not as expected)-CodePudding

Hope you all doing well!

I have these five tables and have an expected outcome for JOIN them.

Example

Table JobShipment

Table Jobheader

Table Branch

Table Company

Table Notetext

My Expected outcome

Though I have tried my best to join the table, the outcome is not what I expected. Was there any part I missed out ?

My query and result

SELECT JS.JS_JobNumber as 'JobNumber', gbb.GB_Code AS 'Branch' , gb.GB_Code as 'Company' ,jh.jh_Dept as 'Dept', ST.ST_NoteText AS 'Note Text'
 FROM notetext st (NOLOCK)  
 LEFT JOIN Company gc (NOLOCK) on st.st_gc_relatedCompany = gc.gc_pk 
 LEFT JOIN jobshipment js (NOLOCK) ON st.ST_ParentID  = js.JS_PK
 LEFT JOIN jobheader jh  (NOLOCK) on jh.jh_parentID =  js.js_pk 
 left JOIN Branch gbb (NOLOCK) on jh.jh_ge = gbb.GB_PK 
 left JOIN Branch gb (NOLOCK) ON GB.gb_company = gc.gc_pk AND gbb.gb_pk = gb.gb_pk
 where JS.JS_JobNumber = 'S0154'

I don't know why the notetext appears in branch 'CLE'. Any guidance would be much appreciated.

CodePudding user response：

I am bad at SQL but gave it a try.

Select jh.jh_parentId as JobNumber,gb.gb_code as Branch,jh.jh_dept as dept, gc.gc_code as Company,st.ST_NoteText as NoteText
from branch gb (NOLOCK)
Inner JOIN Jobheader jh (NOLOCK) on jh.jh_ge=gb.gb_pk
LEFT JOIN Company gc (NOLOCK) on gc.gc_pk=gb.gb_company
Left Join NoteText st (NOLOCK) on st.st_gc_relatedCompany=gb.gb_company

CodePudding user response：

Your JS_JobNumber is related to both companies, so you have to add additional condition that gbb.gb_company = st_gc_relatedCompany

SELECT JS.JS_JobNumber as 'JobNumber', gbb.GB_Code AS 'Branch' , gb.GB_Code as 'Company' ,jh.jh_Dept as 'Dept', ST.ST_NoteText AS 'Note Text'
 FROM notetext st (NOLOCK)  
 LEFT JOIN Company gc (NOLOCK) on st.st_gc_relatedCompany = gc.gc_pk 
 LEFT JOIN jobshipment js (NOLOCK) ON st.ST_ParentID  = js.JS_PK
 LEFT JOIN jobheader jh  (NOLOCK) on jh.jh_parentID =  js.js_pk 
 left JOIN Branch gbb (NOLOCK) on jh.jh_ge = gbb.GB_PK 
 left JOIN Branch gb (NOLOCK) ON GB.gb_company = gc.gc_pk AND gbb.gb_pk = gb.gb_pk
 where JS.JS_JobNumber = 'S0154' AND gbb.gb_company = st.st_gc_relatedCompany

Apart of that, a little advice to try to be consistant in writing style. You it's really difficult to debug code where the same is written in some places lower case than in other places upper case. Other thing is that SQL common standard is to write SQL functions and operators in capitals.