Trying to learn JOIN

Hi there,

Been trying to learn MySQL JOINs but to no avail. Perhaps I threw myself onto a bit too deep waters.

I have two tables, time and multitime:

time:

  	id | date | jobID | staffID | start | finish | total
eg. 8040  	2010-02-04  	1652  	8  	2010-02-02 20:20:00  	2010-02-02 20:40:00  	1200


multitime:

id | taskID | jobID | start | finish | total | staffID
eg.  5742  	8040  	1652  	2010-02-04 03:00:00  	2010-02-04 03:10:00  	600  	8

I need to select total time for every jobID both in time and multitime tables.
I was using this piece of code:

$sel = mysql_query("select distinct jobID from `time` where staffID = '8' && (`start` > '".$_POST['dateIn']." 00:00:01' && `finish` < '".$_POST['dateOut']." 23:59:59')") or die (mysql_error());

while ($row = mysql_fetch_array($sel)) {

   $sel2 = mysql_query("select sum(total) AS totalhrs from `time` where `staffID` = '8' && `jobID` = '".$row['jobID']."' && (`start` > '".$_POST['dateIn']." 00:00:01' && `finish` < '".$_POST['dateOut']." 23:59:59')") or die (mysql_error());
   $row2 = mysql_fetch_array($sel2);
   $mainhours = $row2['totalhrs'];


   $sel2 = mysql_query("select sum(total) AS totalhrs from `multitime` where `staffID` = '8' && `jobID` = '".$row['jobID']."' && `start` > '".$_POST['dateIn']." 00:00:01' && `finish` < '".$_POST['dateOut']." 23:59:59'") or die (mysql_error());
   $row2 = mysql_fetch_array($sel2);
   $subhours = $row2['totalhrs'];

which still didn’t do its job, because if a task was stored only in multitime table (each time can have several multitimes), this was ommited, so the query didn’t return true data.

I guess some sort of JOIN is required here, but I am completely stuck, cannot even make the query work, contanstly making syntax errors.

Can anyone help me out here or guide me?
Any help much much appreciated.

Thanks!
Cheers,
Greg

hint: sounds like you want a UNION

start by writing two separate queries, one for each table

once they’re working, i can show you how to UNION them together

Hey,

thanks for the interest :slight_smile:

So, I have written these two queries for selecting all jobIDs from both tables, this certainly works:


$sel2 = mysql_query("select DISTINCT jobID from `time` where `staffID` = '8' && (`start` > '".$_POST['dateIn']." 00:00:01' && `finish` < '".$_POST['dateOut']." 23:59:59')") or die (mysql_error());
while($row2 = mysql_fetch_array($sel2)) {
  echo $row2['jobID'] . '<br>';
}

$sel2 = mysql_query("select DISTINCT jobID from `multitime` where `staffID` = '8' && `start` > '".$_POST['dateIn']." 00:00:01' && `finish` < '".$_POST['dateOut']." 23:59:59'") or die (mysql_error());
while($row2 = mysql_fetch_array($sel2)) {
  echo $row2['jobID'] . '<br>';
}

Output:

main:
1652
1629

resumed:
1652
1620

Anyways, now, I don’t know how can I select (As a sum()) the column called ‘total’ (as for total time per each task), not to mention that I have no idea how to pull the data merged together :confused:

Thanks for your time, really appreciated!
G

okay, one step at a time

first, could you please run this query outside of php and show the results…

SELECT jobID 
     , NULL AS TaskID
     , total
  FROM `time` 
 WHERE staffID = 8 
   AND `start` >= '2010-02-02 20:20:00'       
   AND `finish` < '2010-02-02 20:49:37'
UNION ALL
SELECT jobID 
     , TaskID
     , total
  FROM `multitime` 
 WHERE staffID = 8 
   AND `start` >= '2010-02-04 03:00:00' 
   AND `finish` < '2010-02-04 03:19:37'
ORDER
    BY jobID
     , TaskID

I run the query you gave me directly in phpmyadmin and it didn’t execute it:

SELECT jobID, NULL AS TaskID, total
FROM `time`
WHERE staffID =8
AND `start` >= '2010-02-02 20:20:00'
AND `finish` < '2010-02-02 20:49:37'
UNION ALL
SELECT jobID, TaskID, total
FROM `multitime`
WHERE staffID =8
AND `start` >= '2010-02-04 03:00:00'
AND `finish` < '2010-02-04 03:19:37'
ORDER
BY jobID, TaskID
LIMIT 0 , 30

MySQL said: Documentation
#1064 - Something is wrong in your syntax obok 'UNION ALL
SELECT jobID
     , TaskID
     , total
  FROM `mu' w linii 8

what version of mysql are you using?

SELECT VERSION()

You need to enclose the two sub-queries in brackets:

From the MySQL Manual:
To use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one. The following example uses both clauses:

(SELECT jobID 
     , NULL AS TaskID
     , total
  FROM `time` 
 WHERE staffID = 8 
   AND `start` >= '2010-02-02 20:20:00'       
   AND `finish` < '2010-02-02 20:49:37')
UNION ALL
(SELECT jobID 
     , TaskID
     , total
  FROM `multitime` 
 WHERE staffID = 8 
   AND `start` >= '2010-02-04 03:00:00' 
   AND `finish` < '2010-02-04 03:19:37')
ORDER
    BY jobID
     , TaskID

there’s another reason i never use the “php” tags on this forum – you can’t really see the stupid green parentheses

however, with “code” tags, especially if i apply the colour myself, they becomes visible –

[COLOR="Red"][B]([/B][/COLOR] SELECT jobID 
     , NULL AS TaskID
     , total
  FROM `time` 
 WHERE staffID = 8 
   AND `start` >= '2010-02-02 20:20:00'       
   AND `finish` < '2010-02-02 20:49:37' [COLOR="Red"][B])[/B][/COLOR]
UNION ALL
[COLOR="Red"][B]([/B][/COLOR] SELECT jobID 
     , TaskID
     , total
  FROM `multitime` 
 WHERE staffID = 8 
   AND `start` >= '2010-02-04 03:00:00' 
   AND `finish` < '2010-02-04 03:19:37' [COLOR="Red"][B])[/B][/COLOR]
ORDER
    BY jobID
     , TaskID

The initial tests I performed via my local host, where the version of mysql is pretty much outdated and the query still cuses an error:

VERSION()
3.23.58-max-debug

But when I run the query on the real thing, it did execute however it did not print anything for me to see, so I changed the dates and hours to include one full day, and then it spat out something:

VERSION()
5.0.89-community-log

jobID 	TaskID 	total
2317 	NULL 	840
2317 	8126 	540
2317 	8126 	720
2347 	NULL 	660
2347 	8127 	1500
2347 	8127 	540

I see it leads somewhere :), I’ll analyze the results to fully understand what has just happend :slight_smile:

Thanks a lot for your time, really appreciated!

Cheers all,
g

UNION is not supported before version 4.0

if you are still running 3.23 on your local host, you really should upgrade

now that you have seen the detail rows produced by the UNION, you can proceed to modify the query to produce the SUMs you want…

I need to select total time for every jobID both in time and multitime tables.

SELECT jobID 
     , SUM(total) AS total_total
  FROM ( SELECT jobID 
              , total
           FROM `time` 
          WHERE staffID = 8 
            AND `start` >= '2010-02-02 20:20:00'       
            AND `finish` < '2010-02-02 20:49:37'
         UNION ALL
         SELECT jobID 
              , total
           FROM `multitime` 
          WHERE staffID = 8 
            AND `start` >= '2010-02-04 03:00:00' 
            AND `finish` < '2010-02-04 03:19:37'
       ) AS u
GROUP
    BY jobID

Wow,

thanks a lot for your help! Really, really :slight_smile:

Best,
Greg

One more question I have.

First of all, this piece of code works as a charm:


SELECT jobID
     , SUM(total) AS total_total
  FROM ( SELECT jobID
              , total
           FROM `time`
          WHERE staffID = 8
            AND `start` > '2010-02-01 00:00:01'
            AND `finish` < '2010-02-28 23:59:59'
         UNION ALL
         SELECT jobID
              , total
           FROM `multitime`
          WHERE staffID = 8
            AND `start` > '2010-02-01 00:00:01'
            AND `finish` < '2010-02-28 23:59:59'
       ) AS u
GROUP
    BY jobID

I wanted to slightly modify the query to show total hours of time and total hours of multitime. A logic step, would be to add SUM() onto both internal (well, within the bracket) queries taking data directly from two tables.

However, when I added it, it immediately produces an error:


SELECT jobID
     , SUM(total) AS total_total
  FROM ( SELECT jobID
              , SUM(total) AS TIME_total
           FROM `time`
...
...

result:


Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

Why can I sum main part of the query, while cannot do same with chunks of it?

SELECT jobID 
     , SUM(t_total) AS time_total
     , SUM(mt_total) AS multitime_total
  FROM ( SELECT jobID 
              , SUM(total) AS t_total
              , NULL AS mt_total
           FROM `time` 
          WHERE staffID = 8 
            AND `start` >= '2010-02-02 20:20:00'       
            AND `finish` < '2010-02-02 20:49:37'
         GROUP
             BY jobID
         UNION ALL
         SELECT jobID 
              , NULL AS t_total
              , SUM(total) AS mt_total
           FROM `multitime` 
          WHERE staffID = 8 
            AND `start` >= '2010-02-04 03:00:00' 
            AND `finish` < '2010-02-04 03:19:37'
         GROUP
             BY jobID
       ) AS u
GROUP
    BY jobID

damn! :slight_smile:

when I see the code in front of my eyes, everything looks so simple!

Thank you so much!
Best,
Greg