select a.*,b.id from pe_member_kind a left join pe_member_enterprise b on a.id=b.idIF MS-SQLselect a.*,b.id,CASE(B.ID) IF(B.ID IS NULL) THEN FLASE ELSE TRUE END AS ISSHOW from pe_member_kind a left join pe_member_enterprise b on a.id=b.id
调试欢乐多
LEFT JOIN pe_member_enterprise ON pe_member_kind.类目=pe_member_kind.类目
where pe_member_kind.类目 is NULL;
这个例子找出在pe_member_kind中所有的行,其"类目"值在pe_member_enterprise中不存在(即,所有pe_member_kind中的在pe_member_enterprise中没有对应行的行)。
$MYSQL->query("select * from pe_member_kind");
$MYSQL1->query("select * from pe_member_kind where kind=".$MYSQL->data[id]);
$count1=$MYSQL1->rows;
if(!($count1))
{
echo "<table cellspacing=0 cellpadding=0>";
echo "<form action='memberkind_mng.php?id=".$MYSQL->data[id]."' method='post' name='form_edit'><tr>";
echo "<td>[<input type='submit' name='submit_del' class='CC' value='删除' onclick='return window.confirm(\"真的要删除吗?\");'>";
echo "]</td></tr></form></table>";
}
其结果是:从表pe_member_kind中取出所有的类目,在其类目附近都显示‘删除’按钮!!
但表pe_member_kind中相应的类目在表pe_member_enterprise 中有相应的详细信息,应该不能显示‘删除’按钮,但全都显示出来了!!该如何解决???
select pe_member_kind.* from pe_member_kind LEFT JOIN pe_member_enterprise ON pe_member_kind.kind=pe_member_kind.kind where kind=pe_member_kind.kind is NULL应该写成
select pe_member_kind.* from pe_member_kind LEFT JOIN pe_member_enterprise ON pe_member_kind.kind=pe_member_kind.kind where pe_member_kind.kind=pe_member_kind.kind is NULL
select pe_member_kind.*,e.类目 from pe_member_kind as k
LEFT JOIN (select DISTINCT 类目 from pe_member_enterprise) as e
ON k.类目=e.类目;
用 DISTINCT 限制pe_member_enterprise.类目,找出在pe_member_kind中所有的行。
若 e.类目 值为null,其"类目"值在pe_member_enterprise中不存在