左连接查询

项目中遇到的情况,需要查询广告主所有的广告及登录的网站主是否申请的状态,广告与网站主的关系存在fh_ad_webmaster 表中。

select 
a.`name` adname,a.`type` adtype,a.`picSize` size,a.`putinType` puttype,
a.`checkType` checktype,a.`startTime` starttime,a.`endTime` endtime,
a.`picPath`,a.`linkText` linktext,
b.`name` name ,b.`squeezeType` squeezeType,
c.`AdMaster` admaster, c.`companyName` comanyname,c.`signPic` signpic,c.`webName` webname,
c.`webUrl` weburl,
d.`catelogName`,d.id cid,
e.`status` entersataus
from `fh_union_admaster` c ,`fh_union_catalog` d,`fh_ad_paynormal` b,
`fh_ad_base` a left OUTER JOIN `fh_ad_webmaster` e on a.`id`=e.`acId` and e.`webMasterId`='ff808081167b0fac01167b1d470e0004'
WHERE a.`getMoneyType`=b.`id` and c.`AdMaster`=a.`merchanter` and d.`id`=c.`webType`
and a.`merchanter`='ff808081167b0fac01167b1c794e0001' and a.`status`=1

 或者

select a.id id,
       e.status status,
       a.name name,
       a.type type,
       a.picSize picSize,
       a.checkType checkType,
       a.startTime startTime,
       a.endTime endTime,
       c.AdMaster AdMaster,
       c.signPic signPic,
       c.webUrl webUrl,
       c.webName webName,
       d.id cid,
       d.catelogName catelogName,
       b.detailExplanation detailExplanation,
       b.squeezePercent squeezePercent,
       b.createTime createTime,
       b.type paytype,
       b.squeeze squeeze,
       c.companyName companyName,
       a.picPath picPath,
       a.linkText linkText
from fh_ad_base a
     left join fh_ad_paynormal b on a.merchanter = b.adMasterId  and a.getMoneyType=b.id
     left join fh_union_admaster c on c.AdMaster = a.merchanter 
     left join fh_union_catalog d on c.webType = d.id
     left join fh_ad_webmaster e on e.acId = a.id and e.webMasterId =
      'ff808081167b0fac01167b1d470e0004'
where a.merchanter = 'ff808081167b0fac01167b1c794e0001' and
      a.status = 1
order by id desc

 通过次sql明白了什么情况下使用left join

阅读更多
文章标签: C C++ C# SQL
个人分类: mysql
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭