SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Addict greg76's Avatar
    Join Date
    Aug 2004
    Location
    Poland
    Posts
    274
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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:
    Code MySQL:
      	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:
    Code MySQL:
    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:
    PHP 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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict greg76's Avatar
    Join Date
    Aug 2004
    Location
    Poland
    Posts
    274
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,

    thanks for the interest

    So, I have written these two queries for selecting all jobIDs from both tables, this certainly works:
    PHP Code:
    $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 :/

    Thanks for your time, really appreciated!
    G

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, one step at a time

    first, could you please run this query outside of php and show the results...
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict greg76's Avatar
    Join Date
    Aug 2004
    Location
    Poland
    Posts
    274
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I run the query you gave me directly in phpmyadmin and it didn't execute it:
    Code MySQL:
    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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what version of mysql are you using?
    Code:
    SELECT VERSION()
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,072
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code SQL:
    (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
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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 --
    Code:
    ( 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict greg76's Avatar
    Join Date
    Aug 2004
    Location
    Poland
    Posts
    274
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    what version of mysql are you using?
    Code:
    SELECT VERSION()
    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:
    Code MySQL:
    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:
    Code MySQL:
    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

    Thanks a lot for your time, really appreciated!

    Cheers all,
    g

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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.
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Addict greg76's Avatar
    Join Date
    Aug 2004
    Location
    Poland
    Posts
    274
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow,

    thanks a lot for your help! Really, really

    Best,
    Greg

  12. #12
    SitePoint Addict greg76's Avatar
    Join Date
    Aug 2004
    Location
    Poland
    Posts
    274
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One more question I have.

    First of all, this piece of code works as a charm:
    Code MySQL:
    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:

    Code MySQL:
    SELECT jobID
         , SUM(total) AS total_total
      FROM ( SELECT jobID
                  , SUM(total) AS TIME_total
               FROM `time`
    ...
    ...
    result:
    Code MySQL:
    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?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Addict greg76's Avatar
    Join Date
    Aug 2004
    Location
    Poland
    Posts
    274
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    damn!

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

    Thank you so much!
    Best,
    Greg


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •