Member Avatar for fabzster

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
  • 121 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by trudge
Member Avatar for nav33n

You can use, sum(ifnull(points,0), points_added). If the 1st expression in ifnull ie., points is null, it returns 0 and if its not null, it returns points value. I too dont know much about joins and related problems, but, this link might be useful.

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

Member Avatar for fabzster

thanks for replying I will give it a try and let u know

Member Avatar for fabzster

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
Member Avatar for trudge

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.