hope my title made it clear - how to add a sub-specifier within the LEFT JOIN condition:
here's my query :
$sql = "SELECT *, serv_names.item_name AS servname,
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 )
WHERE cs_shw='1'
**** AND piclib.pic_hilite='1' *****
ORDER BY cs_dat_usr DESC LIMIT 5";
ok works fine up to a point
the piclib table may contain five pictures but I only want the single picture in there that has been pre-selected to be highlighted - ie pic_hilite=1
I have tried adding the "AND piclib.pic_hilite='1' "
as a sub clause :
LEFT JOIN piclib ON (casestuds.cs_id=piclib.cs_id ... AND )
but no joy - (I know I could do it in two queries - but I'm already doing another query to get a count before the LIMIT so that would then be three)
should it be a Union or is there a more concise way?
thanks
