Select the latest 2 job vacancies from 5 job categories - Help.

[r937]

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

[mattih5]

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 = "
select d.id
, t1.id
, t1.vac_date_added
, t1.vac_reference
, t1.vac_title
from deployment_role as d
inner
join deployment_vacancy as t1
on t1.role_id = d.id
inner
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)
group
by d.id
, t1.id
, t1.vac_date_added
, t1.vac_reference
, t1.vac_title
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.

Please help!

[r937]

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

better reconsider

[mattih5]

Ar, so is it because the date fields are not datetime types?

[r937]

that would explain it not working correctly, yes