select p.cataname,p.id,a.smallpic
from piccatalog p left join pic a
on a.catalogId = p.id
where
(p.userid = 0
or p.userId = @uid)
--and a.cover = 0
我需要查询的情况是当a.cover = 0的时候a.smallpic才被连接到p中,如果没有a.cover = 0的情况,a.smallpic就显示空。
如果加上被注释掉的那行,那就只能查出a.cover = 0.如果注释掉,那就会查出更多的来
from piccatalog p left join pic a
on a.catalogId = p.id
where
(p.userid = 0
or p.userId = @uid)
--and a.cover = 0
我需要查询的情况是当a.cover = 0的时候a.smallpic才被连接到p中,如果没有a.cover = 0的情况,a.smallpic就显示空。
如果加上被注释掉的那行,那就只能查出a.cover = 0.如果注释掉,那就会查出更多的来
from piccatalog p left join pic a
on a.catalogId = p.id and a.cover = 0
where
(p.userid = 0
or p.userId = @uid)
LEFT JOIN 它是以第一个表里的栏位为基准 第二个表里没有的会显示空
结贴
from piccatalog p left join pic a
on a.catalogId = p.id
where
(p.userid = 0
or p.userId = @uid)
on a.catalogId = p.id and a.cover = 0
from piccatalog p left join pic a
on a.catalogId = p.id
and (p.userid = 0 or p.userId = @uid)
from piccatalog p left join pic a
on a.catalogId = p.id
where
(p.userid = 0
or p.userId = @uid)
p.id ,
case when a.cover = 0 then a.smallpic else null end smallpic
from piccatalog p left join pic a
on a.catalogId = p.id
where p.userid = 0 or p.userId = @uid
select p.cataname,p.id,smallpic=case when a.cover = 0 then a.smallpic else null end
from piccatalog p left join pic a
on a.catalogId = p.id
where
(p.userid = 0
or p.userId = @uid)