SQL query help

[n3mesis125]

Hey Folks,

 

I have the below query that I am trying to work out.

 

<?php

$sql = "SELECT SUM(tracker_data.cat_aht) as ecm_prod, SUM(adhoc_data.cat_aht) as adhoc_prod, SUM(`session_total`) as sesstotal,
	SUM(tt_dro.total_aht) as tt_prod, SUM(csit_actioned.total_aht) as csit_prod, SUM(ecw_data.total_aht) as ecw_prod,		
	bot_shifts.date, bot_shifts.shift, bot_shifts.username, tt_dro.date, csit_actioned.entry_date, ecw_data.date,
	tracker_data.date, adhoc_data.date, logins.start_date
	FROM logins, bot_shifts, tracker_data, tt_dro, csit_actioned, ecw_data, adhoc_data
	WHERE bot_shifts.date BETWEEN '2008-10-01' AND '2008-10-20'
	AND logins.start_date=bot_shifts.date
	AND csit_actioned.entry_date=bot_shifts.date
	AND ecw_data.date=bot_shifts.date
	AND adhoc_data.date=bot_shifts.date
	AND tracker_data.date=bot_shifts.date
	AND tt_dro.date=bot_shifts.date
	AND bot_shifts.username='april.mullin'
	AND logins.username=bot_shifts.username
	AND tracker_data.username=bot_shifts.username
	AND ecw_data.username=bot_shifts.username
	AND tt_dro.username=bot_shifts.username
	AND adhoc_data.username=bot_shifts.username
	AND csit_actioned.username=bot_shifts.username
	GROUP BY bot_shifts.date";
?>

 

I have several tables I'm trying to link together here and sum up a column in each table based on the username='april.mullin' and a date range between a specific date. I'm trying to make all tables sum their respective columns if they have the same date range and equal the same username. All tables will have the same username in each. Is there any easier way of doing the above, the above query isn't generating any data for me either :S

 

Thanks,

n3m.

[fenway]

PLEASE use proper ANSI join syntax here.

[n3mesis125]

Im not sure what you mean by that?

 

I've provided a smaller query below to better explain what I mean.

 

SELECT IFNULL(SUM(ecm.cat_aht),0) as ecm_prod, SUM(l.session_total) as sesstotal, s.username, s.date as shiftdate, s.shift
	FROM bot_shifts s, logins l, tracker_data ecm
	WHERE s.date BETWEEN '2008-10-01' AND '2008-10-20'
	AND s.username='april.mullin'
	AND l.start_date=s.date
	AND ecm.date=s.date
	AND l.username=s.username
	AND ecm.username=s.username
	GROUP BY s.date

 

To explain my tables, the shifts table has data like:

 

bot_shifts:

id  date  username  shift 

1, 2008-11-05, john.doe, 28800

2, 2008-11-04, john.doe, 28800

 

logins:

id  username  start_date  session_total 

17134, john.doe, 2008-11-05, 37148

17134, john.doe, 2008-11-04, 11000

 

tracker_data:

data_id  date, username cat_aht 

4, 2008-11-05, john.doe, 123

4, 2008-11-04, john.doe, 100

 

The idea of the above 3 tables is to SUM the shift column for bot_shifts, SUM the session_total column for logins table and SUM the cat_aht column for tracker_data table. However these SUM's are based on the date range of table bot_shifts. So if the range is bot_shifts.date BETWEEN '2008-10-01' AND '2008-10-20' it needs to match the other tables as well as the username. The username is the same in all tables.

 

Thanks,

n3m.

[fenway]

I mean this:

 

SELECT IFNULL(SUM(ecm.cat_aht),0) as ecm_prod, SUM(l.session_total) as sesstotal, s.username, s.date as shiftdate, s.shift
      FROM bot_shifts AS s
      INNER JOIN logins AS l ON ( l.username=s.username AND l.start_date=s.date )
      INNER JOIN tracker_data AS ecm ( ecm.username=s.username AND ecm.date=s.date )
      WHERE s.date BETWEEN '2008-10-01' AND '2008-10-20'
      AND s.username='april.mullin'
      GROUP BY s.date

[n3mesis125]

I've tried your version of the query below but it doesn't return any data, I've posted my php code below as well.

 

<?php

$csc = $_GET['csc'];

require_once('../Connections/rogers.php');
mysql_select_db($database_rogers, $rogers);

$sql = "SELECT IFNULL(SUM(ecm.cat_aht),0) AS ecm_prod, IFNULL(SUM(l.session_total),0) AS sesstotal, s.username, s.date as shiftdate, s.shift
     	FROM bot_shifts AS s
   	   	INNER JOIN logins AS l ON ( l.username=s.username AND l.start_date=s.date )
   	   	INNER JOIN tracker_data AS ecm ON ( ecm.username=l.username AND ecm.date=s.date )
   	   	WHERE s.date BETWEEN '2008-10-01' AND '2008-10-20'
      	AND s.username='april.mullin'
      	GROUP BY s.date";
$res = mysql_query($sql) or die(mysql_error());



$data=array();

while($r = mysql_fetch_assoc($res))
{
$data[] = array(
	'date' => $r['shiftdate'],
	'ecm' => $r['ecm_prod'],
	'session' => $r['sesstotal'],
	'shift' => $r['shift']);
}

print_r($data);


mysql_close($rogers);

?>

[fenway]

I've tried your version of the query below but it doesn't return any data, I've posted my php code below as well.

There's probably a typo there... what was the error?

[n3mesis125]

There wasn't any error, just when I do a print_r($data) it returns Array() and no data is in the array. If I do print_r($r) it returns nothing as well. It seems like when there is no data in the table for that specific user and since I'm doing a SUM() it returns an empty array(). I used a different table then tracker_data and it returned data, however the SUM()'s weren't adding up properly. It seems like the 2nd Sum() is adding the 1st SUM() and itself each row. However the first SUM() works properly.

[fenway]

Well, I don't know about the php part... but you'll need a LEFT JOIN if you want back records without matching rows.