you changed the query -- the SELECT and GROUP BY no longer agree
make sure the GROUP BY has exactly the same columns as the SELECT, and see if that fixes it
i know the query used to work -- i tested it
Sorry to keep nagging but no matter what I do I just can't get this query to work correctly. I am doing what you mentioned in the last post and grouping by all selected items.
Please take a moment to look over, I have attached exactly how the database tables look at this state of time.
Here is my query
$sql = "
from deployment_role as d
join deployment_vacancy as t1
on t1.role_id = d.id
join deployment_vacancy as t2
on t1.role_id = t2.role_id
and t1.vac_date_added <= t2.vac_date_added
where d.id in (1,2,3,4,5)
having count(*) <= 2
And all I keep getting back is record 119, please refer to the screenshot to see which one this is.
I just can't sus it out.
query looks okay, so i decided to test it (again) using your data from post #25
that's when i noticed that you have defined vac_date_added as VARCHAR(100)
you're never going to get accurate date calculations with that, sorry
in fact, almost all your columns are VARCHAR(100), even the numeric ids
Ar, so is it because the date fields are not datetime types?