0

So, I have this SQL schema that I cannot change and I am trying to pull data out and into a Google Sheet using a GAS Script. It returns 0 results. If I remove the last JOIN and the last (corresponding) line of SELECT columns I get 507 results. I know this has something to do with referencing a table that is not directly related to the primary table in the FROM clause. It is one step removed.

I would appreciated a little education here ...

:-)

PS: No I did not write/design/name the schema/tables/columns, and unfortunately I do not have access to modify anything ... just read.

SELECT table_tasktime.tasktime_employee, table_tasktime.tasktime_task,
       table_tasktime.tasktime_start, table_tasktime.tasktime_end,
       table_taskgroups.taskgroup_task, table_taskgroups.taskgroup_group,
       table_employees.employee_id, table_employees.employee_firstname,
       table_employees.employee_lastname, table_tasks.task_name,
       table_groups.group_id, table_groups.group_name
FROM table_tasktime
     INNER JOIN table_taskgroups 
          ON table_taskgroups.taskgroup_task = table_tasktime.tasktime_task
     INNER JOIN table_employees
          ON table_employees.employee_id = table_tasktime.tasktime_employee
     INNER JOIN table_tasks
          ON table_tasks.task_id = table_tasktime.tasktime_task
     INNER JOIN table_groups
          ON table_groups.group_id = table_taskgroups.taskgroup_id
WHERE (table_tasktime.tasktime_start BETWEEN '2016-01-01' AND '2016-01-25 23:59:59')
Mozrin
  • 9
  • 3
  • select distinct taskgroup_id from table_taskgroups Then, select distinct group_id from table_groups. Is there any overlap? Are you comparing like with like? – CustodianOfCode Jan 28 '16 at 00:05
  • If you change the last join to a LEFT JOIN, do you get your 507 results? – Duncan Howe Jan 28 '16 at 00:10
  • Yes ... LEFT join solved the problem. I will read now and try to figure out why. Thank you for getting me over the hump here. – Mozrin Jan 28 '16 at 00:17
  • http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ – Barmar Jan 28 '16 at 00:36
  • I get the results but the data for the variable (table_groups.group_id) and (table_groups.group_name) do not populate from the SELECT. I think this is a problem with the LEFT join. I think what I need is a FULL OUTER JOIN which is lacking in MySQL. This might help ... but I think this process is going to be slower than just pulling in the group table alone and using =QUERY() in Google Sheets to fill in the gap. Thank for all your help everyone. http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql – Mozrin Jan 28 '16 at 01:03
  • @James When you add a table (with an inner join) and your rows drop out then the join is wrong. Does `table_taskgroups` have a `group_id`? It appear there is no relationship with those two columns that you're using. Full outer join won't change anything so don't bother going down that road. – shawnt00 Jan 28 '16 at 01:10
  • table_taskgroups has a group id ... table_tasktime does not have a group id. I have to get the task id from the table_tasktime and then go to the table_taskgroups to get the relationship between task and group and then to the table_groups to get the name of the group. – Mozrin Jan 29 '16 at 01:09
  • @James, I am basically saying then that you're joining on the wrong columns. – shawnt00 Jan 31 '16 at 02:36

1 Answers1

0

A quick run down on joins:- With an INNER JOIN you will get data back where the predicate (the ON) clause is available on both sides of the join. With a LEFT [OUTER] JOIN you can get data from the left table, the table you are joining to, and data from the right table, the table being "attached" where it exists. You can also have RIGHT [OUTER] JOIN which is the opposite of a left join, and CROSS JOIN which will give you the full Cartesian product of the two tables.

Hope this helps you as a starting point. There is lots of stuff out there.

Duncan Howe
  • 2,965
  • 19
  • 18