SQL JOIN - Cant use Alias, can't access the column. Could not be bound/Invalid Column

Question

I have the following example

  SELECT    
        a.PATIENT_ACCOUNT_NUMBER AS Account, 
        a.Payment AS Collection,
        CONCAT(c.FacilityCode, a.PATIENT_ACCOUNT_NUMBER) AS Client_Account,

FROM    TEMP_TABLE_2 a
LEFT JOIN   denial_table d ON a.PATIENT_ACCOUNT_NUMBER= d.account_number

--Issue here
INNER JOIN  events_table e ON Client_Account = e.fk_Account
--

INNER JOIN  flist_table b ON a.FACILITY_CODE = b.client_facility
INNER JOIN  ref_f_table c ON c.ID = b.fk_facility_id

The issue is in the 2nd Join

• I cannot use Alias 'Client_Account'

  • Invalid column name

• I cannot use CONCAT(c.FacilityCode, a.PATIENT_ACCOUNT_NUMBER)

  • Multi-part identifier could not be bound

How can I access CONCAT(c.FacilityCode, a.PATIENT_ACCOUNT_NUMBER) in the 2nd Join??

Using T-SQL

Answer 1

You need to use the full expression (not the alias) and put the tables in the correct order:

SELECT a.PATIENT_ACCOUNT_NUMBER AS Account, a.Payment AS Collection,
       CONCAT(c.FacilityCode, a.PATIENT_ACCOUNT_NUMBER) AS Client_Account,
       . . .
FROM TEMP_TABLE_2 a INNER JOIN
     flist_table b
     ON a.FACILITY_CODE = b.client_facility INNER JOIN 
     ref_f_table c
     ON c.ID = b.fk_facility_id INNER JOIN
     events_table e
     ON CONCAT(c.FacilityCode, a.PATIENT_ACCOUNT_NUMBER) = e.fk_Account LEFT JOIN
     denial_table d
     ON a.PATIENT_ACCOUNT_NUMBER= d.account_number

As a benefit, I think it is better to put the INNER JOINs before the LEFT JOINs. It helps make sure that a NULL-key (from a non-matching row) is not inadvertently used in an ON clause.