Two tables joined but need help getting one row pulled into query

I have two tables joined that are supposed to be pulling in 143 records based off of facility. 142 of the facilities match with Join and work. The problem is one table has a code for just one facility that is different than the other. I just need a way to link that so it pulls into my report. information below and any info much appreciated. Dont ignore the obvious because I am not at all a SQL expert.

INNER JOIN TABLE1.Beds as nbc on bc.FacilityCode = nbc.FacilityCode

bc.FacilityCode has FACILITY A as 111 and nbc.FacilityCode has FACILITY A as 111A. That is the only discrepancy in the table. I tried writing a CASE WHEN statement but it must be where I placed it because it isnt pulling the facility into the column. Any help much appreciated.

Is your database SQLServer?

JOIN clause does not make sense. Shouldn't it be: INNER JOIN TABLE1 AS nbc

Post the enter SQL statement.

Quote:

---

Originally Posted by June7

Is your database SQLServer?

JOIN clause does not make sense. Shouldn't it be: INNER JOIN TABLE1 AS nbc

Post the enter SQL statement.

---

I ended up figuring it out. Basically disregard my JOIN. I changed verbiage and must have mistyped. Here is what solved it.

INNER JOIN BR.NB as nc on CASE WHEN bc.S6='AAA' THEN 'AAA1' ELSE bc.S6 END

I was doing the CASE WHEN outside of the JOIN in the SELECT statement which was too late because the JOIN already ran. This way it took the AAA in table 1 and viewed it as AAA1 in table 2 linking the records. This can be closed. Thanks.