By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,825 Members | 1,281 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,825 IT Pros & Developers. It's quick & easy.

how to get count from 3 different tables

P: 14
hi,
i am using mysql. i have 3 tables with following structures. these are not actual tables i am working on.

table A
id varchar(16),name varchar(255),InDate datetime

table B
id varchar(16),name varchar(255),InDate datetime

table C
id varchar(16),name varchar(255),InDate datetime

what i want to do is obtain no of rows from each table that satisfies the following condition.
in the webpage user selects two dates. i have to obtain count from each table(A,B,C) where InDate in each table falls inbetween the user selected dates. i.e if user selects march 1st and march 30 then, from table A i have to obtain no.of rows where InDate falls in between march 1st and march 30. similarly from each table.

the query should result something like this

mar1st mar2nd mar3rd.................. mar30
A 10 5 NULL
B 2 8 7
C 5 3 9

assuming that A contains 10 rows where InDate is mar1,5 rows where InDate is mar2nd. no rows for mar3.

i am trying with following queries. i am using php.
Expand|Select|Wrap|Line Numbers
  1. $query=
  2. select
  3. (select count(*) from A where date(InDate) between date('2009-03-01 00:01:01') and date('2009-03-29 00:01:01')) as A-count,
  4. (select count(*) from B where date(InDate) between date('2009-03-01 00:01:01') and date('2009-03-29 00:01:01')) as B-count,
  5. (select count(*) from C where date(InDate) between date('2009-03-01 00:01:01') and date('2009-03-29 00:01:01'))as C-count;
  6.  
the above query is giving total count. but not datewise.
Expand|Select|Wrap|Line Numbers
  1.  
  2.                 $query = NULL;
  3.                 $tempquery = NULL;
  4.  
  5.                 $dt=25;
  6.                 $month = 2;
  7.                 $year = 2009;
  8.                 $flag = true;
  9.                 $enddate = '2009-3-30';
  10.  
  11.                 while($flag === true){
  12.                                 if(checkdate($month,$dt,$year)){
  13.                                                 $date = $year.'-'.$month.'-'.$dt;
  14.                                 }else if(checkdate($month+1,1,$year)){
  15.                                                 $month = $month+1;
  16.                                                 $dt = 1;
  17.                                                 continue;
  18.                                 }else if(checkdate($month,1,$year+1)){
  19.                                                 $year = $year+1;
  20.                                                 $dt = 1;
  21.                                                 continue;
  22.                                 }
  23.                                 if(strtotime($date)>strtotime($enddate)){
  24.                                                 $flag = false;
  25.                                                 break;
  26.                                 }
  27.                                 $datestr = date('MjS',strtotime($date));
  28.                                 if($tempquery == NULL){
  29.                                                 $tempquery = "(SELECT COUNT(*) FROM A  WHERE DATE(InDate) BETWEEN DATE('".$date." 00:01:01')
  30.                                                 AND DATE('".$date." 23:59:59')) AS  A_".$datestr;
  31.                                 }else{
  32.                                                 $tempquery .= ",(SELECT COUNT(*) FROM A  WHERE DATE(InDate) BETWEEN DATE('".$date." 00:01:01')
  33.                                                 AND DATE('".$date." 23:59:59')) AS A_".$datestr;                        
  34.                                 }
  35.                                 $dt++;
  36.                 }
  37.  
  38.                 $query = "SELECT ".$tempquery;
  39.  
  40.  
this query is giving datewise count. but for only one table. how can i improve this to get count from remaining tables.

otherwise if there is any better way to implement this, pls suggest that

thanks
Mar 30 '09 #1
Share this Question
Share on Google+
1 Reply


code green
Expert 100+
P: 1,726
You do not need the DATE() function call.
Expand|Select|Wrap|Line Numbers
  1. select count(*) from A where InDate 
  2. between '2009-03-01 00:01:01' and '2009-03-29 00:01:01' 
  3. as A-count
is all that is needed.
But I am not sure what difference it will make.
Apr 6 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.