select a.guid,a.classname,isnull(b.allowinsert,0) as allowinsert from t_menu1 a
left join t_popedom1 b on a.guid=b.classid
where b.userid='liujz'
left join t_popedom1 b on a.guid=b.classid
where b.userid='liujz'
Union All
Select guid,classname, '0' as allowinsert from t_menu1 Where guid Not In (Select classid from t_popedom1 Where userid='liujz' )
Order by guid,classname
guid classname allowinsert
10001 菜单b 1
10004 菜单e 1
case
when b.allowinsert=1 then 1
when b.allowinsert is null then 0
end
from t_menu1 a left join t_popedom1 b on a.guid=b.classid and b.userid='liujz'
----------- -------------------------------------------------- -----------
10000 菜单a 0
10001 菜单b 1
10002 菜单c 0
10003 菜单d 0
10004 菜单e 1
10005 菜单f 0
select a.guid,a.classname,allowinsert=IsNull(b.allowinsert,0)
from t_menu1 a left join t_popedom1 b on a.guid=b.classid and b.userid='liujz'
guid int NULL ,
classname varchar (50) NULL ,
allowinsert int null)insert into #allowinsert select *,0 from t_menu1update #allowinsert set allowinsert = 1 from #allowinsert a,t_popedom1 b where a.guid = b.classid and b.userid = 'liujz'select * from #allowinsert
from t_menu1 a left join t_popedom1 b on a.guid=b.classid and b.userid='liujz'