Trying to get a Join to return null values

You are in : Recent Topics » Zoho Reports » Reports Bugs » Trying to get a Join to return null values

Trying to get a Join to return null values

danaeshell

on 23-Jul-2009 02:20 AM.

I'm sure I'm doing something stupid here, but i've been struggling with this query for ages so I've got to give up and ask!

I have two tables that i'm trying to create a report with.

1. A users table, with two columns - a user ID and a staff member name

2. A time entries table with 3 columns - the user's id, the date, and the number of hours

(i've simplified the tables to just the columns i'm worried about)

I want to run a report that allows us to see who hasn't filled out their time for any given month. So that means that if Table 1 has these entries:

user_id_1 UserName1

user_id_2 UserName2

user_id_3 UserName3

and Table 2 has these entries

user_id_1 1-8-09 4

user_id_1 2-8-09 5

user_id_3 5-8-09 5

After grouping by month, I would like a report that says:

UserName1 9 (hours)

UserName2 null

UserName3 5

Here is the SQL query that I have at the moment:

SELECT "Users"."Staff Member Name" AS "Staff Member", "Time Entries"."Activity" AS "Activity", "Time Entries"."Hours" AS "Hours", WEEKOFYEAR("Time Entries"."Date") AS 'Week'

FROM "Users" 

LEFT JOIN "Time Entries" on "Time Entries"."Added User" = Users."User ID"

What i'm getting back looks like this:

UserName1 1-8-09 4

UserName2 1-8-09 4

UserName3 1-8-09 4

UserName1 2-8-09 5

UserName2 2-8-09 5

UserName3 2-8-09 5

etc. -- it seems to be repeating each line entry for each person listed int he User table, rather than just listing the users who haven't got any matching entries with a null value.

Can someone help me figure out where I'm going wrong?

Thank you very much!

No status
Working on it
Answered
Need more info

1 user has this question

rajeshkota

on 23-Jul-2009 12:59 PM

Hi,

Sorry for the inconvenience caused with the issue in the query table. Whenever a lookup column is used along with left join the Query table fetches the cartesian product of the joined tables. We acknowledge that this is an issue and will fix the same at the earliest. However, we have worked out a query which would fetch the expected results. Please find below the same,

" SELECT "Users"."Staff Member Name" AS "Staff Member",
SUM( IF ("Users."User ID"="Time Entries"."User ID","Time Entries"."Hours",0) AS "Hours",
Month("Time Entries"."Date") AS 'Week'
FROM "Users" INNER JOIN "Time Entries" GROUP BY "Users"."Staff Member Name",MONTH( "Time Entries"."Date") ORDER BY MONTH("Time Entries"."Date") "

Kindly try the above query and let us know for any further assistance on this.

Thanks for your support to us and assuring the best of our service.

Regards,
Rajesh.K
Zoho Reports

rajeshkota

on 11-Nov-2009 10:05 AM

Hi,

Thanks for your patience. We have fixed the issue,which was discussed in this thread and updated the service with the same. Kindly try the above query which you have tried earlier and let us know for any further clarifications.

Thanks for the co-operation and assuring the best of our service.

Regards,
Rajesh.K
Zoho Reports