Results 1 to 4 of 4

Thread: MySQL Help

  1. #1
    Join Date
    Jun 2008
    Posts
    49

    Unanswered: MySQL Help

    I have some problems on how to output this...

    Here's an example of my table

    table1

    -------------------------------------------------------
    jobid transcode dtestarted startedby
    -------------------------------------------------------
    0001 AR 2008-06-15 07:42:26 dave
    0001 SH 2008-06-18 17:21:10 justine
    0002 AR 2008-06-18 08:21:10 dave
    0002 SH 2008-06-18 17:30:10 justine
    0003 AR 2008-06-18 17:21:10 jonas
    0003 SH 2008-06-18 18:21:10 cruz

    I must output the number counts of jobid of every person who's transcode is AR and I must display the dtestarted of that certain jobid where the transcode is SH

    I did the query like this:

    select
    count(jobid) as `counts`,
    startedby,
    dtestarted
    from table1 where transcode = 'AR'
    group by startedby
    But I have no idea on how to output the dtestarted of the jobid (where the transcode is SH) along with it...
    Last edited by BlueGemini; 06-18-08 at 04:16.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    I must output the number counts of jobid of every person who's transcode is AR
    Code:
    SELECT startedby
         , COUNT(jobid) AS `counts`
      FROM table1 
     WHERE transcode = 'AR'
    GROUP 
        BY startedby
    and I must display the dtestarted of that certain jobid where the transcode is SH
    Code:
    SELECT row_SH.jobid
         , row_SH.dtestarted
      FROM table1 AS row_AR
    INNER
      JOIN table1 AS row_SH
        ON row_SH.jobid = row_AR.jobid
     WHERE row_AR.transcode = 'AR'
       AND row_SH.transcode = 'SH'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2008
    Posts
    49
    Quote Originally Posted by r937
    I must output the number counts of jobid of every person who's transcode is AR
    Code:
    SELECT startedby
         , COUNT(jobid) AS `counts`
      FROM table1 
     WHERE transcode = 'AR'
    GROUP 
        BY startedby
    and I must display the dtestarted of that certain jobid where the transcode is SH
    Code:
    SELECT row_SH.jobid
         , row_SH.dtestarted
      FROM table1 AS row_AR
    INNER
      JOIN table1 AS row_SH
        ON row_SH.jobid = row_AR.jobid
     WHERE row_AR.transcode = 'AR'
       AND row_SH.transcode = 'SH'
    It work! Thank you! Thank you very much!

    I really appreciated it.

  4. #4
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    its like,you make 2 tables out of 1 table and place it on a variable.
    and when you have 2 tables you can now select what fields you need from the source table.
    great, nice tricks.

    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

Posting Permissions

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