•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 397,645 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,408 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MySQL advertiser:
Views: 626 | Replies: 5 | Solved
 |
•
•
Join Date: Aug 2007
Location: Cape Town, South Africa
Posts: 6
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
Mysql Joins Assistance 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
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
•
•
Join Date: Nov 2007
Location: Bangalore, India
Posts: 3,098
Reputation:
Rep Power: 8
Solved Threads: 238
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/...functions.html
http://dev.mysql.com/doc/refman/5.0/...functions.html
Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.
*PM asking for help will be ignored*
*PM asking for help will be ignored*
•
•
Join Date: Nov 2007
Location: Bangalore, India
Posts: 3,098
Reputation:
Rep Power: 8
Solved Threads: 238
You can also see this.. http://dev.mysql.com/doc/refman/5.0/...ction_coalesce
Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.
*PM asking for help will be ignored*
*PM asking for help will be ignored*
•
•
Join Date: Aug 2007
Location: Cape Town, South Africa
Posts: 6
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
thanks for replying I will give it a try and let u know
•
•
Join Date: Aug 2007
Location: Cape Town, South Africa
Posts: 6
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
Re: Mysql Joins Assistance Hi
I found the answer on another forum hope this helps someone out
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 •
•
Join Date: Sep 2007
Location: North Bay Ontario
Posts: 174
Reputation:
Rep Power: 1
Solved Threads: 20
Junior Poster
•
•
•
•
Originally Posted by fabzster 
Hi
I found the answer on another forum hope this helps someone out
Then please flag this thread as 'SOLVED' in the first posting.
Amer Neely - Web Mechanic
"Others make web sites. We make web sites work!"
"Others make web sites. We make web sites work!"
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb MySQL Marketplace
Linear Mode
Similar Threads
- MS SQL Joins - newb assistance (MS SQL)
Other Threads in the MySQL Forum
- Previous Thread: Problem regarding mysql query: it is displaying duplicate contents
- Next Thread: Favorite 3rd party software with MySQL
