Sql join help - basic

[eponcede]

I want to create a new table that outputs the columns (red box) by selecting the objects in black squares from the different tables.
I am stuck at this point, where I get the right # of rows, but dont get any more info and have tried different joins with no luck.

I am looking for values in the dbo.Messages table where Value = -5 as those rows have a NULL value in ResponseCptCode and want to change it to something else...maybe i need to 2 to new tables, not sure.
Any help would be much appreciated.

SELECT
t1.SuperBillId,
t1.Response,
t1.ResponseCptCode,
l1.Code
FROM Messages AS t1
LEFT JOIN Cpts as l1
 ON l1.Id = t1.Id
WHERE t1.Value	=-5
GROUP BY t1.SuperBillId,t1.Response,t1.ResponseCptCode,l1.Code

[bitsmed]

According to your diagram, id in message table doesn't link to cpts table. You have to "go thru" superbillcpts table. Something like this:

select a.superbillid
      ,a.response
      ,a.responsecptcode
      ,c.code
  from dbo.messages as a
       inner join dbo.superbillcpts as b
               on b.superbillid=a.superbillid
       inner join dbo.cpts as c
               on c.id=b.cptid
 where a.[value]=-5