Mysql query problems

Question

0.00/5 (No votes)

See more:

I have three tables one with the information about the client (name, user name), another table with the service performed to is car (points (obtained because of the service), service made) and another with the company representing where the client works(name, location).
I'm trying to make a query to get together the company client works, the client, and the sum of all the points obtained.
I already tried several approaches, but all are failing. Some of them gets me duplicated rows and others missing.
I would like to display a client information even if it doesn't have any points.

Can anyone help me please?

This is what I already tried:

SQL

SELECT *FROM client c
         INNER JOIN company b ON c.company_idcompany  = b.idcompany
         INNER JOIN (select sum(pointos) as pointos From services) d

SQL

SELECT *FROM client c
        INNER JOIN company b ON c.company_idcompany  = b.idcompany
        INNER JOIN service d ON c.idclient = d.client_idclient

Posted 26-Feb-12 3:10am

Member 2266542

Updated 26-Feb-12 3:16am

André Kraak

v2

Add a Solution

Comments

André Kraak 26-Feb-12 9:16am

Edited question:
Added pre tags
Formatted text/code
Spelling/Grammar

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

idle63 · Answer 1 · 2012-02-26T07:20:00

Solution 1

Something like below should help (+- some syntax errors :))

SQL

SELECT
   c.*,
   b.*,
   (SELECT SUM(pointos) FROM services WHERE services.client_idclient = c.client_idclient)  sum_points
FROM client c
LEFT JOIN company b ON c.company_idcompany  = b.idcompany

Posted 26-Feb-12 7:20am

idle63

Bernhard Hiller · Answer 2 · 2012-02-26T21:57:00

You need a UNION: a sub-query sums up the points for those clients having points, and another query gets the clients without points.

SQL

SELECT client.name as clientname, company.name as companyname, company.location, SUM(pointos) as points
FROM client 
INNER JOIN company ON client.company_idcompany  = company.idcompany
INNER JOIN service ON client.idclient = service.client_idclient
GROUP BY client.name

UNION

SELECT client.name as clientname, company.name as companyname, company.location, 0 as points
FROM client
INNER JOIN company ON client.company_idcompany  = company.idcompany
LEFT JOIN service ON client.idclient = service.client_idclient
WHERE service.client_idclient IS NULL