MySQL的左连接限制
我想限制的最后一个左连接到只有1结果MySQL的左连接限制
$query="SELECT a.*, cc.name AS category, ee.ezcity AS proploc
, dd.name AS statename, bb.name AS countryname
, u.logo_image AS logo_image, u.mid AS mid
, u.dealer_name AS dealer_name, u.dealer_company AS dealer_company
, u.dealer_phone AS dealer_phone, u.dealer_mobile AS dealer_mobile
, u.published AS dealerpublished, sp.tenant AS tenant
, sp.spacenum AS spacenum, sp.sf AS sf, sp.image AS tenantimage
, u.dealer_type AS dealer_type
FROM #__ezrealty as a"
. "\n LEFT JOIN #__ezrealty_catg AS cc ON cc.id = a.cid"
. "\n LEFT JOIN #__ezrealty_locality AS ee ON ee.id = a.locid"
. "\n LEFT JOIN #__ezrealty_state AS dd ON dd.id = a.stid"
. "\n LEFT JOIN #__ezrealty_country AS bb ON bb.id = a.cnid"
. "\n LEFT JOIN #__ezrealty_profile AS u ON u.mid = a.owner"
. "\n LEFT JOIN #__ezrealty_siteplan AS sp ON sp.listing_id = a.id "
. "\n WHERE $extrastring AND a.published = '1' $vacant
AND cc.access <= $my->gid $wheres "
. $order.' LIMIT '.$pageNav->limitstart.', '.$pageNav->limit;
我曾尝试:
. "\n LEFT JOIN (SELECT listing_id FROM #__ezrealty_siteplan LIMIT 1) sp
ON sp.listing_id = a.id"
我觉得你的结果看起来像它含有超过1,因为结果不是GROUP BY的任何列。
GROUP BY与聚合使用。巧合的是,它会消除重复,但这远远不是它的设计目的。如果一个查询需要一个GROUP BY来消除重复,通常意味着查询是错误设计的。 SELECT子句上的DISTINCT限定符比仅包含SELECT子句中的一部分列名的GROUP BY子句更简单且更具可移植性,该子句允许MySQL允许,但其他DBMS不允许。 – 2011-04-18 21:14:33
不是任何mysql专家,但我无法找到很多帮助,我可以将它拼凑在一起,它似乎工作: 左加入(SELECT * FROM #__ezrealty_siteplan GROUP BY listing_id)AS SP ON sp.listing_id = a.id 我会选择你的答案,因为它指向我粗暴地完成我所需要的工作(如果我理解正确,应该有更好的方法来做到这一点) – MSD 2011-04-19 14:48:57
它看起来像你从ezrealty_siteplan随机选择'listing_id'?你怎么知道这会匹配'a.id'?如果你不把'(SELECT listing_id FROM #__ezrealty_siteplan LIMIT 1)'改为'(SELECT listing_id FROM #__ezrealty_siteplan WHERE listing_id = .... LIMIT 1)' – Johan 2011-04-18 22:39:12
对于你想要做的事情非常困惑。如果你将'LEFT JOIN'改为'INNER JOIN',这有帮助吗? – Johan 2011-04-18 22:40:43
我用它解决了这个问题: LEFT JOIN(SELECT * FROM #__ezrealty_siteplan GROUP BY listing_id)AS sp ON sp.listing_id = a.id 问题是每个房地产上市是一个上市本身(整个购物中心)和列表需要包含购物中心中租赁和可用空间的子列表。当用户搜索时,如果购物中心有5个可用空间,它将显示相同的购物中心5倍。 – MSD 2011-04-19 14:42:16