请教多表查询语句写法？

SQL code

  
   select c.id,
          c.corporationName,
          c.corporationNetName,
          nvl(m.PersonCount, 0) PersonCount,
          cg.name corpcategoryName,
          c.city || ' ' || c.section || ' ' || c.street area
          
     from (select f.toCorpId
             from follow f
            where f.state = 1
              and f.corpid = 1323008
            group by f.toCorpId) f
    inner join Corporation c
       on f.toCorpId = c.id
    inner join Corpservice s
       on c.id = s.corporationid
      and s.servicename = 5
      and s.state = 1
    inner join corpcategory cg
       on c.categoryid_l0 = cg.id
       
     left join 
     
     (select m.merchant, count(1) PersonCount
                  from abc m
                 group by m.merchant) m
       on c.customername = m.merchant
      
    where c.id not in (select c.id
                         from corporation c
                        where c.fatherid = 1323008
                          and c.state = 1)
      and c.id not in (select decode(p.corpid,1323008,p.tocorpid,p.corpid) corpid
                      from couponbroadcast p
                     where p.tocorpid = 1323008 or p.corpid = 1323008
                       and p.state != 3
                       and p.state != 4) 

上面的SQL语句已经测试，能正常执行并查询出数据。

现在我想在 couponbroadcast 表中再拿一个state字段值出来。请问该怎样写？谢谢！

couponbroadcast 并不在 或者生成的集合在from 中
只在where中存在呢。

个人感觉用（+）会简单很多

couponbroadcast 只存在于子查询中 当然查不了state字段 要么再单独连下表couponbroadcast

给你个简单但是不效率的方法，在select部位加上一个子查询：
SQL code

 select c.id,
          c.corporationName,
          c.corporationNetName,
          nvl(m.PersonCount, 0) PersonCount,
          cg.name corpcategoryName,
          c.city || ' ' || c.section || ' ' || c.street area，
          （select state from couponbroadcast where ......） -- where条件要保证结果集最多1条记录
  from (select f.toCorpId

试试这个 
SQL code

select c.id,
          c.corporationName,
          c.corporationNetName,
          nvl(m.PersonCount, 0) PersonCount,
          cg.name corpcategoryName,
          c.city || ' ' || c.section || ' ' || c.street area,
          st.state
          
     from (select f.toCorpId
             from follow f
            where f.state = 1
              and f.corpid = 1323008
            group by f.toCorpId) f
    inner join Corporation c
       on f.toCorpId = c.id
    inner join Corpservice s
       on c.id = s.corporationid
      and s.servicename = 5
      and s.state = 1
    inner join corpcategory cg
       on c.categoryid_l0 = cg.id
       

       
     left join 
     
     (select m.merchant, count(1) PersonCount
                  from abc m
                 group by m.merchant) m
       on c.customername = m.merchant
       
            left join   (select decode(p.corpid,1323008,p.tocorpid,p.corpid) corpid, p.state
                      from couponbroadcast p
                     where p.tocorpid = 1323008 or p.corpid = 1323008
                       and p.state != 3
                       and p.state != 4) st
      on c.id=st.corpid
    where c.id not in (select c.id
                         from corporation c
                        where c.fatherid = 1323008
                          and c.state = 1)
          and st.corpid is null