Limits a left join?
Hi I have a query that pulls the results from a search but I want the left joined results to be limited to 1
Here is the left join:
LEFT JOIN #__ezrealty_siteplan AS sp ON sp.listing_id = a.id "
What I have tried
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)
The entire query:
$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;
In SQL Server, you could use CROSS APPLY. In other DBMS, you can use partitioning (windowing function ROW_NUMBER).
For MySQL, one solution is to use a subquery to get LIMIT 1, then join that to the table, e.g.
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
I thinks this will give you what you want
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
精彩评论