Left out join from two tables fails

I am trying to LEFT JOIN the table called CHRRES which has two keys in it from 2 other tables, but I cannot find the right way to order the JOIN and FROM clauses.
EMPHIS.EMP_RES joins to CHRRES.CRS_RES and CHRHIS.CHH_CHR joins to CHRRES.CRS_CHR.
It must be a LEFT join as there is some data that exists in the main select that does not have joining data in the CHRRES table and that is fine, but I need all the rows returned regardless.

Although the SQL below is specific to Oracle it also fails in SQL Server (example 3) with a similar error.

How can I write this so that it will work everytime?

Doing it like this gives me an invalid identifier for EMH_RES. If I reverse the FROM clause to have CHRHIS followed by EMPHIS then the invalid identifier becomes the CHH_CHR column.

  SELECT PSD_KEY,PSH_PEDATE,To_Char(To_Date(PSH_PEDATE,'YYYYMMDD'),'DD-MON-YYYY') AS EXP_PEDATE,PSD_DATE,
      To_Char(To_Date(PSD_DATE,'YYYYMMDD'),'DD-MON-YYYY') AS EXP_DATE,
      EMH_CODE,PSL_PAPROJ,PSL_PRJ,CHH_FLD3,PSL_RAT_CD,CASE WHEN PSD_MIN=0 THEN 0 WHEN PSD_MIN <> 0 THEN PSD_MIN/60 END AS PSD_HRS,
      SUBSTR(A.TCN_NOTE,1,240) AS PSL_NOTES,EMH_FLD6,RAT_FLD1,PSL_FLD4,PSL_FLD5,
      PSL_FLD1,SubStr(PSL_FLD2,INSTR(PSL_FLD2, '.', 1, 1)+1) AS PSL_FLD2,SubStr(PSL_FLD3,INSTR(PSL_FLD3, '.', 1, 2)+1) AS PSL_FLD3,SubStr(B.TCN_NOTE,1,200) AS PSH_NOTES
      FROM PSDETAIL,EMPHIS,CHRHIS LEFT JOIN CHRRES ON (CRS_CHR=CHH_CHR AND CRS_RES=EMH_RES),
  PSHEADER LEFT JOIN TCNOTES B ON (B.TCN_AKEY=PSH_KEY AND B.TCN_FIELD='PSH_NOTES'),
      PSLINES LEFT JOIN TCNOTES A ON (A.TCN_AKEY=PSL_KEY AND A.TCN_FIELD='PSL_NOTES')
      LEFT JOIN RATE ON RAT_CODE=PSL_RAT_CD
      WHERE PSD_MIN <> 0 AND (PSD_PADATA IS NULL OR PSD_PADATA=' ') AND PSD_PSL=PSL_KEY AND PSL_PSH=PSH_KEY AND PSL_CHH=CHH_KEY AND PSH_EMH=EMH_KEY
 
I tried this thinking this might work by separating the LEFT joins and then tying them together in the WHERE clause, and although I get results I am clearly missing rows so this is not working correctly.

  SELECT PSD_KEY,PSH_PEDATE,To_Char(To_Date(PSH_PEDATE,'YYYYMMDD'),'DD-MON-YYYY') AS EXP_PEDATE,PSD_DATE,
      To_Char(To_Date(PSD_DATE,'YYYYMMDD'),'DD-MON-YYYY') AS EXP_DATE,
      EMH_CODE,PSL_PAPROJ,CHH_FLD3,PSL_RAT_CD,CASE WHEN PSD_MIN=0 THEN 0 WHEN PSD_MIN <> 0 THEN PSD_MIN/60 END AS PSD_HRS,
      SUBSTR(A.TCN_NOTE,1,240) AS PSL_NOTES,EMH_FLD6,RAT_FLD1,PSL_FLD4,PSL_FLD5,
      PSL_FLD1,SubStr(PSL_FLD2,INSTR(PSL_FLD2, '.', 1, 1)+1) AS PSL_FLD2,SubStr(PSL_FLD3,INSTR(PSL_FLD3, '.', 1, 2)+1) AS PSL_FLD3,SubStr(B.TCN_NOTE,1,200) AS PSH_NOTES,
  B.CRS_RACCT
      FROM PSDETAIL,EMPHIS LEFT JOIN CHRRES A ON (A.CRS_RES=EMPHIS.EMH_RES),CHRHIS LEFT OUTER JOIN CHRRES B ON (CRS_CHR=CHRHIS.CHH_CHR),
  PSHEADER LEFT JOIN TCNOTES B ON (B.TCN_AKEY=PSH_KEY AND B.TCN_FIELD='PSH_NOTES'),
      PSLINES LEFT JOIN TCNOTES A ON (A.TCN_AKEY=PSL_KEY AND A.TCN_FIELD='PSL_NOTES')
      LEFT JOIN RATE ON RAT_CODE=PSL_RAT_CD
      WHERE PSD_MIN <> 0 AND (PSD_PADATA IS NULL OR PSD_PADATA=' ') AND PSD_PSL=PSL_KEY AND PSL_PSH=PSH_KEY AND PSL_CHH=CHH_KEY AND PSH_EMH=EMH_KEY AND A.CRS_KEY=B.CRS_KEY

Here is an example that fails in SQL Server with "The multi-part identifier "EMPHIS.EMH_RES" could not be bound."

SELECT PSD_KEY,PSH_PEDATE,PSH_PEDATE,PSD_DATE,
      EMH_CODE,CHH_FLD3,PSL_RAT_CD,CASE WHEN PSD_MIN=0 THEN 0 WHEN PSD_MIN <> 0
    THEN PSD_MIN/60 END AS PSD_HRS,
      EMH_FLD6,RAT_FLD1,
      PSL_FLD1,PSL_FLD2,PSL_FLD3,B.TCN_NOTE AS PSH_NOTES
      FROM PSDETAIL,EMPHIS,CHRHIS LEFT JOIN CHRRES ON (CRS_CHR=CHRHIS.CHH_CHR AND CRS_RES=EMPHIS.EMH_RES),
  PSHEADER LEFT JOIN TCNOTES B ON (B.TCN_AKEY=PSH_KEY AND B.TCN_FIELD='PSH_NOTES'),
      PSLINES LEFT JOIN TCNOTES A ON (A.TCN_AKEY=PSL_KEY AND A.TCN_FIELD='PSL_NOTES')
      LEFT JOIN RATE ON RAT_CODE=PSL_RAT_CD
      WHERE PSD_MIN <> 0 AND PSD_PSL=PSL_KEY AND PSL_PSH=PSH_KEY AND PSL_CHH=CHH_KEY AND PSH_EMH=EMH_KEY;
hmstechsupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Your syntax is off in the SQL Server example.  What are the commas doing in the JOIN clauses?

FROM PSDETAIL,EMPHIS,CHRHIS
LEFT JOIN CHRRES
   ON (CRS_CHR=CHRHIS.CHH_CHR AND CRS_RES=EMPHIS.EMH_RES),
  PSHEADER

LEFT JOIN TCNOTES B ON (B.TCN_AKEY=PSH_KEY AND B.TCN_FIELD='PSH_NOTES'),
0
Brian CroweDatabase AdministratorCommented:
I just noticed the joining clauses at the end.  There are no table qualifiers so I'm making an assumption.
0
Anthony PerkinsCommented:
It would really help your cause if you include the aliases for all the columns.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PortletPaulfreelancerCommented:
you really should NOT mix ANSI joins and old fashioned joining (via the where clause)
going 100% ANSI syntax will definitely help

here are the joining structures of the first queries (formatted)
FROM PSDETAIL
	, EMPHIS                                 --<< non ansi
	, CHRHIS                                 --<< non ansi
LEFT JOIN CHRRES ON (
		CRS_CHR = CHH_CHR
		AND CRS_RES = EMH_RES
		)
	, PSHEADER                                 --<< non ansi
LEFT JOIN TCNOTES B ON (
		B.TCN_AKEY = PSH_KEY
		AND B.TCN_FIELD = 'PSH_NOTES'
		)
	, PSLINES                                 --<< non ansi
LEFT JOIN TCNOTES A ON (
		A.TCN_AKEY = PSL_KEY
		AND A.TCN_FIELD = 'PSL_NOTES'
		)
LEFT JOIN RATE ON RAT_CODE = PSL_RAT_CD
WHERE PSD_MIN <> 0
	AND (
		PSD_PADATA IS NULL
		OR PSD_PADATA = ' '
		)
	AND PSD_PSL = PSL_KEY                     --<< non ansi join
	AND PSL_PSH = PSH_KEY                     --<< non ansi join
	AND PSL_CHH = CHH_KEY                     --<< non ansi join
	AND PSH_EMH = EMH_KEY                     --<< non ansi join

Open in new window

HOWEVER throughout the above you omit to identify which tables own what fields; so this is my GUESS:
SELECT

-- please include tables as prefixes in the select clause e.g. PSDETAIL.PSD_KEY

       PSD_KEY
     , PSH_PEDATE
     , To_Char(To_Date(PSH_PEDATE, 'YYYYMMDD'), 'DD-MON-YYYY') AS EXP_PEDATE
     , PSD_DATE
     , To_Char(To_Date(PSD_DATE, 'YYYYMMDD'), 'DD-MON-YYYY') AS EXP_DATE
     , EMH_CODE
     , PSL_PAPROJ
     , PSL_PRJ
     , CHH_FLD3
     , PSL_RAT_CD
     , CASE 
          WHEN PSD_MIN = 0
               THEN 0
          WHEN PSD_MIN <> 0
               THEN PSD_MIN / 60
          END AS PSD_HRS
     , SUBSTR(A.TCN_NOTE, 1, 240) AS PSL_NOTES
     , EMH_FLD6
     , RAT_FLD1
     , PSL_FLD4
     , PSL_FLD5
     , PSL_FLD1
     , SubStr(PSL_FLD2, INSTR(PSL_FLD2, '.', 1, 1) + 1) AS PSL_FLD2
     , SubStr(PSL_FLD3, INSTR(PSL_FLD3, '.', 1, 2) + 1) AS PSL_FLD3
     , SubStr(B.TCN_NOTE, 1, 200) AS PSH_NOTES
FROM PSDETAIL
INNER JOIN PSLINES  ON PSDETAIL.PSD_PSL = PSLINES.PSL_KEY
INNER JOIN CHRHIS   ON PSLINES.PSL_CHH = CHRHIS.CHH_KEY
INNER JOIN PSHEADER ON PSLINES.PSL_PSH = PSHEADER.PSH_KEY
INNER JOIN EMPHIS   ON PSHEADER.PSH_EMH = EMPHIS.EMH_KEY
LEFT JOIN CHRRES    ON (
                        CHRHIS.CHH_CHR = CHRRES.CRS_CHR
                        AND EMPHIS.EMH_RES = CHRRES.CRS_RES
                        )
LEFT JOIN TCNOTES B ON (
                        PSHEADER.PSH_KEY = B.TCN_AKEY
                        AND B.TCN_FIELD = 'PSH_NOTES'
                        )
LEFT JOIN TCNOTES A ON (
                        PSLINES.PSL_KEY = A.TCN_AKEY
                        AND A.TCN_FIELD = 'PSL_NOTES'
                        )
LEFT JOIN RATE      ON PSLINES.PSL_RAT_CD = RATE.RAT_CODE
WHERE PSDETAIL.PSD_MIN <> 0
        AND (
                PSDETAIL.PSD_PADATA IS NULL
                OR PSDETAIL.PSD_PADATA = ' '
                );

Open in new window

{+ 2 edits, sorry}
{+ one more, sorry again,  an " AND " had moved into a bad place}
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hmstechsupportAuthor Commented:
Wow.  

This is exactly what I need!  I guess my lesson learned for today is the "you really should NOT mix ANSI joins and old fashioned joining (via the where clause) going 100% ANSI syntax will definitely help"

This was extremely helpful.  I have yet to compare the contents columns by column, but the rows returned are the exact number I expected so that's a really good sign.
Thank you for your advice and the actual SQL which fired off beautifully as is so you guessed correctly.
0
PortletPaulfreelancerCommented:
:) good guess then, cheers, Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.