0

Hi

I would really appreciate some assistance on the following as I suck a bit when it comes to joins

i have 3 tables consultants,points,orders

all tables have the column id_number

I need to run a query that will give me a list of all the consultants with a total from points and a total from orders

when I do a left join with 2 tables then I get the correct answer, the minute I add the 3rd I get incorrect data

the query:

SELECT
consultants.name,
consultants.surname,
consultants.cell_number,
consultants.register_status,
consultants.id_number,
Sum(points.points_added),
Sum(orders.points_cost)
FROM
consultants
Left Join points ON points.id_number = consultants.id_number
left Join orders ON orders.id_number = consultants.id_number
GROUP BY
consultants.name,
consultants.surname,
consultants.cell_number,
consultants.register_status,
consultants.id_number

I thing the problem lies in that some consultants do not have any orders and may not have been allocated points

your assistance will be greatly appreciated

3
Contributors
5
Replies
6
Views
9 Years
Discussion Span
Last Post by trudge
0

Hi

I found the answer on another forum hope this helps someone out

SELECT consultants.name
     , consultants.surname
     , consultants.cell_number
     , consultants.register_status
     , consultants.id_number
     , pts.pts_added as points_added
     , ords.pts_cost as points_cost
  FROM consultants
LEFT OUTER
  JOIN ( SELECT id_number 
              , SUM(points_added) AS pts_added
           FROM points
         GROUP
             BY id_number ) AS pts
    ON pts.id_number = consultants.id_number
LEFT OUTER
  JOIN ( SELECT id_number 
              , SUM(points_cost) AS pts_cost
           FROM orders
         GROUP
             BY id_number ) AS ords
    ON ords.id_number = consultants.id_number
0

Hi
I found the answer on another forum hope this helps someone out

Then please flag this thread as 'SOLVED' in the first posting.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.