Code: Select all
$query = 'SELECT a.*
FROM yyf3h_bmzr_staff a
INNER JOIN
(
SELECT clientid, staffid, COUNT(*) totalCount
FROM yyf3h_bmzr_relations
WHERE franchiseeid = ' . $owner . '
AND status < 9
GROUP BY staffid
having count(case when clientid = ' . $clientid . ' then 1 end) = 0
) b ON a.id = b.staffid
WHERE a.owner = ' . $owner . '
AND a.status < 9
AND a.name LIKE \'%' . $search . '%\'
';
Code: Select all
$db =& JFactory::getDBO();
$q =& $db->getQuery(true);
$q->select(array('a.*'));
$q->from($this->db->quoteName('#__bmzr_staff', 'a'));
Thank you!