MySQL Help

BlueGemini · #1 (**permalink**) 06-18-08, 03:12

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:

Quote:

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...

r937 · #2 (**permalink**) 06-18-08, 07:22

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'

BlueGemini · #3 (**permalink**) 06-18-08, 07:55

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.

homer.favenir · #4 (**permalink**) 06-20-08, 02:46

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech