Mysql Join help

Expand Post »

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

MySQL Syntax (Toggle Plain Text)
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
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

Similar Threads

Optimize, Fix MySQL Join Query in PHP
MySQl Join Help Needed! in MySQL
MySql multiple table query problem.... in MySQL

fabzster

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

30 posts
since Aug 2007

Permalink

Dec 19th, 2008

Re: Mysql Join help

Try this

MySQL Syntax (Toggle Plain Text)
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
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

varmadba

Reputation Points: 22

Solved Threads: 9

Junior Poster in Training

Offline

83 posts
since Jun 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: error:when special characters are inserted

Next Thread in MySQL Forum Timeline: Display mysql data

DaniWeb Message

Cancel Changes

User Name
Password