限制左联接？

| 嗨，我有一个查询，它从搜索中提取结果，但我希望将左侧联接的结果限制为1 这是左联接：

LEFT JOIN #__ezrealty_siteplan AS sp ON sp.listing_id = a.id \"

我尝试过的

 LEFT JOIN (SELECT * FROM jos_ezrealty LEFT JOIN jos_ezrealty_siteplan AS sp ON (sp.listing_id =jos_ezrealty.id ) LIMIT 1) AS sp ON (sp.listing_id=jos_ezrealty.id)

整个查询：

   $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;

已邀请:

3 个回复

襄挟款籍

在SQL Server中，您可以使用CROSS APPLY。在其他DBMS中，可以使用分区（窗口功能ROW_NUMBER）。对于MySQL，一种解决方案是使用子查询获取LIMIT 1，然后将其连接到表，例如

select a.col1, a.col2, a.col3, b.col1, b.col2
from
(
select a.col1, a.col2, a.col3, (select b.id as b_id from tbl_b b where b.a_id=a.id limit 1)
from tbl_a a
) a
left join tbl_b b on b.id=a.b_id

捻盒愧杯

我认为这会给你你想要的

SELECT DISTINCT sp.id ,* FROM jos_ezrealty LEFT JOIN jos_ezrealty_siteplan AS sp ON sp.listing_id =jos_ezrealty.id

抽法

LEFT JOIN ( SELECT * 
            FROM #__ezrealty_siteplan
            GROUP BY listing_id )
       AS sp 
       ON sp.listing_id = a.id

要回复问题请先登录或注册

限制左联接？

3 个回复

发起人

mysql

sql

问题状态

限制左联接？

与内容相关的链接

3 个回复

发起人

mysql

sql

问题状态