[RESOLVED] limit 1 within left join - is it possible?

cretaceous

I am trying to do two multiple left joins AND two more left joins where I only want the first result of each of those two returned

so this is the base query which works fine:

SELECT casestuds.cs_id AS curcs, serv_names.item_name AS servname, 
serv_names.item_id AS servid, clients.item_name AS cliname, piclib.pic_name AS pic_hi_name 
FROM  casestuds 
LEFT JOIN serv_names ON (casestuds.cs_serv=serv_names.item_id) 

LEFT JOIN clients ON (clients.item_id=casestuds.cs_cli) 
LEFT JOIN piclib ON (casestuds.cs_id=piclib.cs_id AND piclib.pic_hilite='1') 
WHERE cs_shw='1'  

ORDER BY cliname

now I want to left join two more tables "sct_match" and "spac_match" which might have 0 to several matches for each main item but I only want the first one it finds

so this kind of works:

SELECT casestuds.cs_id AS curcs, serv_names.item_name AS servname, 
serv_names.item_id AS servid, clients.item_name AS cliname, piclib.pic_name AS pic_hi_name 
FROM  casestuds 
LEFT JOIN serv_names ON (casestuds.cs_serv=serv_names.item_id) 

LEFT JOIN sct_match ON (casestuds.cs_id=sct_match.cs_id) 
LEFT JOIN spac_match ON (casestuds.cs_id=spac_match.cs_id )

LEFT JOIN clients ON (clients.item_id=casestuds.cs_cli) 
LEFT JOIN piclib ON (casestuds.cs_id=piclib.cs_id AND piclib.pic_hilite='1') 
WHERE cs_shw='1' AND sct_match.item_id='12' 
ORDER BY cliname

but if the category I am not searching on has several matches it returns the item that number of times, which I understand - but I only want to see it once

not sure how to approach this, any help much appreciated

thanks

Roger_Ramjet

Well you can do this is by using sub-queries which contain the LIMIT clause and joining to them instead of the tables directly

...
LEFT JOIN (SELECT * FROM   casestuds  LEFT JOIN sct_match ON (casestuds.cs_id=sct_match.cs_id) WHERE ....  LIMIT 1) AS t1 ON (casestuds.cs_id=t1.cs_id)
etc etc

I'm sure someone else, probably Lars, will come up with a more elegent solution but that will work untill they do.

cretaceous

thanks Roger - actually I couldnt your suggestion to work but this part of the query was a filter rather than main search, so once I did:

if($p) $sqlmain .=" LEFT JOIN spac_match ON (casestuds.cs_id=spac_match.cs_id ) ";
    if($t) $sqlmain .=" LEFT JOIN sct_match ON (casestuds.cs_id=sct_match.cs_id) ";

as soon as I had a value to join on the product joins went away