Mysql Join help

[fabzster]

Hi

Im not sure if i need a join here, i cant seem to grasp the concept of joins really batteling with this

I allocate points to id numbers in the points table and i have a list of all id numbers in the consultants table

the id numbers only exist in the points table if points where allocated to them

I would like to be able to see what consultants did not earn any points in a specific date range eg a month. so I would like a list of all the missing Id numbers in the points table for a specific month

SELECT consultants.name
, consultants.surname
, consultants.cell_number
, consultants.id_number
FROM consultants
LEFT OUTER
JOIN ( SELECT id_number
FROM points where date_added between '2008-01-01' and '2008-01-30'
GROUP
BY id_number ) as pts
ON pts.id_number = consultants.id_number

this only shows me the consultants that have points in the month

I would really appreciate some help

[varmadba]

Try this

SELECT consultants.name
, consultants.surname
, consultants.cell_number
, consultants.id_number
,points.id_number
FROM consultants
LEFT OUTER JOIN  points 
   on  points.id_number = consultants.id_number
  and date_added between '2008-01-01' and '2008-01-30'
having points.id_number is null