Left Join Not Working & Multi-part identifier could not be bound

I have two queries which are supposed to return similar results, one being the reverse of the other. However only one of them works.

Query No 1 - version 1 - Works Correctly:

SELECT CASE WHEN d.description LIKE 'Dom%' Then 'Domestic' ELSE 'Non-Domestic' END AS PropTypeDecode,
       CAST(p.reference AS VARCHAR(10)) + CAST(p.propcheckdigit AS VARCHAR(1)) AS PropRef,
          p.propstat,
       CAST(c.reference AS VARCHAR(10)) + CAST(C.custcheckdigit AS VARCHAR(1)) AS CustRef
  FROM dbo.pproperty p
       JOIN dbo.sdecode d on p.proptype = d.itemcode and d.groupcode = 'proptype'
          LEFT JOIN dbo.ccustomer c on p.occcustno = c.reference
    order by p.reference+0 asc

It returns the results in the type of layout which I am looking for and allows me to identify all property numbers (left table) and associated customer numbers (right table) including those properties that do not have an associated customer number - 'custref' returned as 00.

My second query needs to do the opposite - return a list of all customer numbers (left table) and their associated property numbers (right table), and return 00 where there is no associated property number. It also needs to identify if the returned property number is domestic or non-domestic, similar to the results of the first query. This is how I tried to write this second query:

Query No 2 - version 1

SELECT CASE WHEN d.description LIKE 'Dom%' Then 'Domestic' ELSE 'Non-Domestic' END AS PropTypeDecode,
       CAST(c.reference AS VARCHAR(10)) + CAST(C.custcheckdigit AS VARCHAR(1)) AS CustRef,
       c.floating,
       CAST(p.reference AS VARCHAR(10)) + CAST(p.propcheckdigit AS VARCHAR(1)) AS PropRef,
       p.propstat
  FROM dbo.ccustomer c
       JOIN dbo.sdecode d on p.proptype = d.itemcode and d.groupcode = 'proptype'
          LEFT JOIN dbo.pproperty p on c.reference = p.occcustno
    order by c.reference+0 asc

However the only result I get is:

Msg 4104, Level 16, State 1, Line 7 The multi-part identifier "p.proptype" could not be bound.

So to attempt to fix this t see if it was the only problem, I removed the CASE WHEN line and the JOIN dbo.sdecode line. However I basically get the same table as returned in my very first query just with a different column order which is not what I want. In this case it appears the LEFT JOIN is matching the customer numbers as the right table to the property numbers as the left table, and not the other way around.

Query No 2 - version 2

SELECT CAST(c.reference AS VARCHAR(10)) + CAST(C.custcheckdigit AS VARCHAR(1)) AS CustRef,
       c.floating,
       CAST(p.reference AS VARCHAR(10)) + CAST(p.propcheckdigit AS VARCHAR(1)) AS PropRef,
       p.propstat
  FROM dbo.ccustomer c
          LEFT JOIN dbo.pproperty p on c.reference = p.occcustno
    order by c.reference+0 asc

I am not entirely sure what is happening as I thought I'd worked out how JOINs work, now I am confused. I have read through some posts on here but I can't see how their solutions work in this instance.

My ideal solution would be to have Query No 1 and No 2 Version 1 however the multipart identifier prevents 2.1 from working initially, and if fixed, does the 2.2 error then take effect?

Any help is greatly appreciated.

Thanks Liam

Solution

This is your original FROM clause that works:

FROM dbo.pproperty p JOIN
     dbo.sdecode d 
     ON p.proptype = d.itemcode and d.groupcode = 'proptype' LEFT JOIN
     dbo.ccustomer c 
     ON p.occcustno = c.reference

If you want to return all customers, then start with all ccustomer as the first table and then use LEFT JOINs and appropriate conditions:

FROM dbo.ccustomer c  LEFT JOIN
     dbo.property p
     ON p.occcustno = c.reference LEFT JOIN
     dbo.sdecode d 
     ON p.proptype = d.itemcode and d.groupcode = 'proptype'

Do note that d.description could be NULL because there might be no matching row. That is p may not match c and d depends on p.