MYSQL JOINS ASSISTANCE

[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

[fenway]

I'm not sure you really want a three-table join here....

[fabzster]

thanks for the reply .....do u have any advice for me?

[fenway]

Well, let's try the join you have first... why all the group by columns, isn't ID enough?

[fabzster]

If I dont use a join what would my other options be??

[fenway]

Well, they're two totally different queries... why run them together?