MySQL

Mysql Join help

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Aug 2007

Posts: 10

Reputation: fabzster is an unknown quantity at this point

Solved Threads: 0

fabzster

Offline

Newbie Poster

Mysql Join help

Dec 19th, 2008

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

 Help with Code Tags 
 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

•

Join Date: Jun 2008

Posts: 79

Reputation: varmadba is an unknown quantity at this point

Solved Threads: 8

varmadba

Offline

Junior Poster in Training

Re: Mysql Join help

Dec 19th, 2008

Try this

 Help with Code Tags 
 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

:- Varma

We are Happy to inform launch of a new site with loads of database related information Site offers wide range of functionality Forums,Blogs,Articles,Editorials and much more
http://www.sqllibrarian.info/