fabzster Posted December 13, 2007 Share Posted December 13, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/81519-mysql-joins-assistance/ Share on other sites More sharing options...
fenway Posted December 13, 2007 Share Posted December 13, 2007 I'm not sure you really want a three-table join here.... Quote Link to comment https://forums.phpfreaks.com/topic/81519-mysql-joins-assistance/#findComment-413865 Share on other sites More sharing options...
fabzster Posted December 13, 2007 Author Share Posted December 13, 2007 thanks for the reply .....do u have any advice for me? Quote Link to comment https://forums.phpfreaks.com/topic/81519-mysql-joins-assistance/#findComment-413872 Share on other sites More sharing options...
fenway Posted December 13, 2007 Share Posted December 13, 2007 Well, let's try the join you have first... why all the group by columns, isn't ID enough? Quote Link to comment https://forums.phpfreaks.com/topic/81519-mysql-joins-assistance/#findComment-413921 Share on other sites More sharing options...
fabzster Posted December 13, 2007 Author Share Posted December 13, 2007 If I dont use a join what would my other options be?? Quote Link to comment https://forums.phpfreaks.com/topic/81519-mysql-joins-assistance/#findComment-413983 Share on other sites More sharing options...
fenway Posted December 14, 2007 Share Posted December 14, 2007 Well, they're two totally different queries... why run them together? Quote Link to comment https://forums.phpfreaks.com/topic/81519-mysql-joins-assistance/#findComment-414447 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.