sql server 2008 - Changing a SQL statement when columns have been extracted to a different table -

we have database keeps track of client issues. have been tasked adding site tab, , part of this, extracted address information dbo.client table, , created new dbo.address table.

i new sql, have managed muddle way through modifying stored procedures. however, have 1 method creates dynamic sql statement, , since tries access address information dbo.client table rather new dbo.address table, , not work.

one possible iteration of statement below:

select iclientid,      (select top 1 isstype.cistypdesc          issue              inner join status on issue.istatusid = status.istatusid              left outer join priorty on issue.ipriortyid = priorty.ipriortyid              left outer join isstype on issue.iisstypeid = isstype.iisstypeid          issue.iclientid = client.iclientid              , issue.istatusid <> 2          order status.nrank desc, priorty.nrank desc, isstype.nrank desc)  cistypdesc,     (select top 1 status.cstatdesc          issue              inner join status on issue.istatusid = status.istatusid              left outer join priorty on issue.ipriortyid = priorty.ipriortyid              left outer join isstype on issue.iisstypeid = isstype.iisstypeid          issue.iclientid = client.iclientid              , issue.istatusid <> 2          order status.nrank desc, priorty.nrank desc, isstype.nrank desc)  cstatdesc,     (select top 1 priorty.cpriority          issue              inner join status on issue.istatusid = status.istatusid              left outer join priorty on issue.ipriortyid = priorty.ipriortyid              left outer join isstype on issue.iisstypeid = isstype.iisstypeid          issue.iclientid = client.iclientid              , issue.istatusid <> 2          order status.nrank desc, priorty.nrank desc, isstype.nrank desc)  cpriority,cname,ccity,cstateid,czip,isupplvlid  client  client.ccity 'sea%' , client.isupplvlid <> 4 

when run this, following errors:

msg 207, level 16, state 1, line 32 invalid column name 'ccity'. msg 207, level 16, state 1, line 30 invalid column name 'ccity'. msg 207, level 16, state 1, line 30 invalid column name 'cstateid'. msg 207, level 16, state 1, line 30 invalid column name 'czip'. 

i can remove references ccity, cstateid, , czip execute without errors, many more rows want, , not of information need.

a helpful answer show need work again have dbo.address table (that matches off of address.iclientid = client.iclientid. more explain needs done, can myself.

i can see each of inner selects getting piece of information specific table. know inner join returns data columns match, while left outer join returns columns left table match right column. however, when these start stack up, i'm not sure how interact. plus, when took out "where client.ccity 'sea%'", got thousands of rows rather 4. if add of commands in parenthesis, limit down 4 again?

this sql statement little on basic understanding.

you need join client address information both tables used occur in 1 table. example:

from dbo.client c  inner join dbo.address on a.iclientid = c.iclientid a.ccity 'sea%' ... 

you should removing of top (1) subqueries in select list - can facilitated simple join too, suspect; possibly outer join, because i'm not sure if there isn't 1:0 mapping there. perhaps:

select   c.iclientid,   x.cistypdesc,   x.cstatdesc,   x.cpriority,   c.cname,   a.ccity,   a.cstateid,   a.czip,   c.isupplvlid  dbo.client c inner join dbo.address on c.iclientid = a.iclientid left outer join  (   select iclientid, cistypdesc, cstatdesc, cpriority       (     select i.iclientid, i.cistypdesc, s.cstatdesc, p.cpriority,       rn = row_number() on (partition i.iclientid          order s.nrank desc, p.nrank desc, t.nrank desc)       dbo.issue      inner dbo.[status] s on i.istatusid = s.istatusid       left outer join dbo.priorty p on i.ipriortyid = p.ipriortyid       left outer join dbo.isstype t on i.iisstypeid = t.iisstypeid       i.istatusid <> 2    ) y rn = 1 ) x  on x.iclientid = c.iclientid a.ccity 'sea%' , c.isupplvlid <> 4;