Page 1 of 1

Please Help Urgent

Posted: Wed Nov 24, 2010 5:21 am
by dheeraja
Hi Guys,
I am scratching my head to fetch the data from two tables having some conditions.

Suppose I am having a table called "table1" having the field
Table 1
[id(int) auto_inc, owner (int), supervisor (int), startdate (date), enddate(date), status (tinyint)]

report_table
[id(int) auto_inc, owner (int), supervisor(int), prdate(date), status(tinyint), project(int), component(int), workdone(text)]

Now I want to know that the supervisor from table1 with startdate say "2010-11-20" and enddate "2010-11-27" submitted how many reports in report_table. that means I want to count the number of reports from 2nd table and arrange it in ascending order on the basis of count.

So please anyone help me...

Re: Please Help Urgent

Posted: Thu Nov 25, 2010 10:28 am
by Darhazer
You need something like:

Code: Select all

select table1.*, count(report_table).id as reports_count FROM
table1 inner join report_table using(supervisor)
where  startdate = "2010-11-20" and enddate  = "2010-11-27"
GROUP BY supervisor ORDER BY reports_count ASC