CODEWITHSUNDEEP
sql
user14624536
user14624536

Reputation:

Get null records in SQL

I have the next query:

    SELECT c.name as clientName, p.id as projectId, p.name as projectName, p.rate, u.name as userName, sum(w.duration) as workedHours
    FROM Project p, User u, Worklog w, Client c
    WHERE w.user_id = u.id AND w.project_id = p.id AND p.client_id = c.id
    GROUP BY p.id, u.id

that returns the projects, clients, hourly rate and worked hours.

How should be changed to return also the projects where workedHours is equal with 0?

Because this query returns just the records where workedHours is not 0.

Thank you for your time.

Upvotes: 0

Views: 75

Answers (2)

Fabian Pijcke
Fabian Pijcke

Reputation: 3210

The problem is that no row in worklog can be joined, and that your condition in the WHERE clause removes any row without worklog associated.

Solution 1 : Using a LEFT JOIN

Using a left join instead would solve your problem.

SELECT c.name as clientName, p.id as projectId, p.name as projectName, p.rate, u.name as userName, coalesce(sum(w.duration), 0) as workedHours
FROM Project p, User u, Client c
LEFT JOIN Worklog w ON w.project_id = p.id AND w.user_id = u.id
WHERE p.client_id = c.id
GROUP BY p.id, u.id

By the way your query is suspicious in other aspects. For example c.name is in the SELECT clause but not in the GROUP BY clause. I take it that you use MySQL which is the only RDBMS I'm aware of which allows such queries. You maybe should consider adding the retrieved columns in the GROUP BY clause.

Solution 2 : Using only ANSI JOINs

As underscore_d points out, you may want to avoid old-style joins completely, and preferable use the following query :

SELECT
  c.name as clientName,
  p.id as projectId,
  p.name as projectName,
  p.rate,
  u.name as userName,
  coalesce(sum(w.duration), 0) as workedHours
FROM Project p
CROSS JOIN User u
INNER JOIN Client c ON p.client_id = c.id
LEFT JOIN Worklog w ON w.project_id = p.id AND w.user_id = u.id
GROUP BY c.name, p.id, p.name, p.rate, u.id, u.name

Solution 3 - Using a subquery

Another solution is to use a subquery, which would allow you to remove the GROUP BY clause completely and get a more manageable query if you ever need to retrieve more information. I personally don't like long lists of columns in a GROUP BY clause.

SELECT
  c.name as clientName,
  p.id as projectId,
  p.name as projectName,
  p.rate,
  u.name as userName,
  (SELECT SUM(duration) FROM Worklog WHERE project_id = c.id AND user_id = u.id) as workedHours
FROM Project p
CROSS JOIN User u
INNER JOIN Client c ON p.client_id = p.id

Upvotes: 1

Popeye
Popeye

Reputation: 35900

You should use standard ANSI joins and use LEFT JOIN on worklog table and ultimately you have to use LEFT JOIN on the user table as follows:

SELECT C.NAME   AS CLIENTNAME,
       P.ID     AS PROJECTID,
       P.NAME   AS PROJECTNAME,
       P.RATE,
       U.NAME   AS USERNAME,
       SUM(W.DURATION) AS WORKEDHOURS
  FROM PROJECT   P
  JOIN CLIENT    C
ON P.CLIENT_ID = C.ID
  LEFT JOIN WORKLOG   W
ON W.PROJECT_ID = P.ID
  LEFT JOIN USER      U
ON W.USER_ID = U.ID
 GROUP BY P.ID,
          U.ID;

Upvotes: 1

Related Questions