-1

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

Example

Table JobShipment

enter image description here

Table Jobheader

enter image description here

Table Branch

enter image description here

Table Company

enter image description here

Table Notetext

enter image description here

My Expected outcome

enter image description here

The outcome is not what I expected.

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'

enter image description here

Why does notetext appear in branch 'CLE'?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Nick
  • 71
  • 1
  • 5
  • 1
    Are you sure you're using MySQL? – jarlh Nov 03 '22 at 09:40
  • 2
    Move the WHERE clause condition the ON clause, and see what happens. – jarlh Nov 03 '22 at 09:43
  • [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) – philipxy Nov 03 '22 at 16:38
  • Please before considering posting: Pin down code issues via [mre]. Read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] – philipxy Nov 03 '22 at 16:39
  • Possible duplicate of [Left Join With Where Clause](https://stackoverflow.com/q/4752455/3404097) – philipxy Nov 03 '22 at 16:40

2 Answers2

0

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
naveen renold
  • 21
  • 3
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 07 '22 at 18:28
0

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.

Amikot40
  • 38
  • 7
  • Hi, thanks for your attention. I tried your query, but the row of branch 'CLE' will disappear. Its only show the branch 'MUB' which is not what my expectation result. – Nick Nov 03 '22 at 15:32