0

I am joining three tables and I am trying to show the latest date but the dates that are being returned are not the latest.

SELECT c.id, c.companyname,c.displayname, c.address1, c.address2,
c.town, c.postcode,cd.contactname cd.contactphone, cd.contactemail, 
MAX(q.coldate)
FROM companies c, query q, custd cd
WHERE c.id = q.id AND q.id = cd.compid
AND year(q.coldate) >= 2016
GROUP BY q.companyname;

How can I fix this please?

Niall
  • 518
  • 1
  • 6
  • 23

1 Answers1

0

if you want to display the max(date) in your table (and therefore the same date) for every row of your result, try this before you run the query:

select cast(max(coldate) as char) from schema_name.table_name into    
@max_coldate_for_query ;

when running your query change coldate to @max_coldate_for_query in the list of columns you are selecting

iLikeMySql
  • 736
  • 3
  • 7
  • That seems to get the latest query date and show that for all. Where as I'm trying to show each company and the latest query belonging to that company, but thank you. – Niall Oct 05 '17 at 15:46
  • is it possible that there is no corresponding entry in custd? you are using inner join which eliminates all records of table query that don't have a corresponding record in table custd. i would recommend to use´ANSI join syntax and change the second join to a left join (between query q and custd cd) if that is ok. – iLikeMySql Oct 05 '17 at 15:50