Menu
Hi there! I have the following query :
SELECT p.prospectno AS BDR,
p.fantasia AS NOME,
p.cidade AS Cidade,
p.uf AS UF,
CONCAT(‘(‘,SUBSTRING(p.cp_fone,1,2),’)’,’ ‘,SUBSTRING(p.cp_fone,3,4),’-‘,SUBSTRING(p.cp_fone,5,4)) AS TELEFONE,
po.descricao as PORTE,
MAX(DATE_FORMAT(h.date,’%s’)) AS ‘ULTIMO CONTATO’,
p.pj AS pj
FROM crmProspects p, crmPorte po, crmHistory h
WHERE p.crmPorte_porteno = po.porteno
AND p.prospectno = h.crmProspects_prospectno
GROUP BY p.prospectno
ORDER BY nome
This query selects all prospects in a table and show them to the user. My problem is, with the max(), and group by, it shows only prospects which have an entry on the crmHistory table. How can I retreive prospects that also do not have an entry on that table, and in that case, return a “-” on the date field for example?
Im using mysql 3.23 for this.
thanks