2. PHPRunner General questions
  3. Trying To Get This To Work!
This topic is locked

Trying To Get This To Work!

 		2/2/2013 12:25:01
PHPRunner General questions
S
 swanside author

I am trying to get this to work.

The idea is, an engineers time can be imported in more than one table, so I need to get the total time for an engineer.
All the tables are linked by EngineerId on teh accounts table to the userPK on the engineers reports table.

I can get it working just using one table, but when I add another, thats where my problems start.

Any help please guys?

Cheers

Paul.
SELECT

account.User_Name,

account.EngineerId,

IFNULL((SELECT dtdimports.R085),0.00) AS DTDStartTime,

IFNULL((SELECT dtdimports.R086),0.00) AS DTDEndTime,

IFNULL((SELECT dtdimports.R003),0) AS DTDDateOfWork,

IFNULL((SELECT gasandelecimport.R085),0.00) AS ApplianceStartTime,

IFNULL((SELECT gasandelecimport.R086),0.00) AS ApplianceEndTime,

IFNULL((SELECT gasandelecimport.R003),0) AS ApplianceDatOfWork,

(SELECT (TIMEDIFF(dtdimports.R086,dtdimports.R085))) AS DayToDayTimeTaken,

(SELECT (TIMEDIFF(gasandelecimport.R086,gasandelecimport.R085))) AS ApplianceTimeTaken,

(SELECT (DayToDayTimeTaken+ApplianceTimeTaken)) AS OverallTime

FROM account

INNER JOIN dtdimports ON account.EngineerId = dtdimports.userPK

INNER JOIN gasandelecimport ON account.EngineerId = gasandelecimport.userPK
N
 		nti 2/3/2013

Calcs do not work with virtual field names; do they?... I'm still trying to figure this question out.



(SELECT (DayToDayTimeTaken+ApplianceTimeTaken)) AS OverallTime


Would require a calc based on the previous two calcs.
Something like:



(SELECT TIMEDIFF(dtdimports.R086,dtdimports.R085) + TIMEDIFF(gasandelecimport.R086,gasandelecimport.R085) AS 'Overall Time',
S
 		swanside author 2/4/2013



Calcs do not work with virtual field names; do they?... I'm still trying to figure this question out.
Would require a calc based on the previous two calcs.
Something like:



(SELECT TIMEDIFF(dtdimports.R086,dtdimports.R085) + TIMEDIFF(gasandelecimport.R086,gasandelecimport.R085) AS 'Overall Time',



Thanks mate, but its not quite that part thats givng the problem yet
If I have this I get results,

SELECT

account.User_Name,

account.EngineerId,

dtdimports.R085 AS DTDStartTime,

dtdimports.R086 AS DTDEndTime,

dtdimports.R003 DTDDateOfWork

FROM account

INNER JOIN dtdimports ON account.EngineerId = dtdimports.userPK



but as soon as I add another table I do not get any results

SELECT

account.User_Name,

account.EngineerId,

IFNULL((SELECT dtdimports.R085),0.00) AS DTDStartTime,

IFNULL((SELECT dtdimports.R086),0.00) AS DTDEndTime,

IFNULL((SELECT dtdimports.R003),0) AS DTDDateOfWork,

IFNULL((SELECT gasandelecimport.R085),0.00) AS ApplianceStartTime,

IFNULL((SELECT gasandelecimport.R086),0.00) AS ApplianceEndTime,

IFNULL((SELECT gasandelecimport.R003),0) AS ApplianceDatOfWork,

(SELECT (TIMEDIFF(dtdimports.R086,dtdimports.R085))) AS `DayToDayTimeTaken`,

(SELECT (TIMEDIFF(gasandelecimport.R086,gasandelecimport.R085))) AS `ApplianceTimeTaken`,

(SELECT (DayToDayTimeTaken+ApplianceTimeTaken)) AS OverallTime

FROM account

INNER JOIN dtdimports ON account.EngineerId = dtdimports.userPK

INNER JOIN gasandelecimport ON account.EngineerId = gasandelecimport.userPK
Sergey Kornilov admin 2/4/2013

Probably you do not have any matching record in gasandelecimport table or join is not setup properly (using wrong fields to link tables).
S
 		swanside author 2/4/2013



Probably you do not have any matching record in gasandelecimport table or join is not setup properly (using wrong fields to link tables).


The two tables are both copied in the MySQL database with the same fields and Id's

If one table is empty, it will not display anything.
C
 		chuckbower 2/7/2013

Do you need to use LEFT OUTER JOIN rather than INNER JOIN?
S
 		swanside author 2/7/2013

I did this and it works after a fashion.

I might need to look at this a different way as I want to get the total time and engineer spends on a job, but there are about 10 other jobsheet tables, here I have two dtdimports and gasandelecimport, I really need to get dtdimports.R085 AS DTDStartTime and gasandelecimport.R085 AS CateringStartTime to be in one field but I ma not sure how I can write two or more fileds from different tables into one field.

SELECT

dtdimports.Job_No,

account.User_Name,

account.EngineerId,

dtdimports.customerPK,

dtdimports.locationPK,

dtdimports.R085 AS DTDStartTime,

dtdimports.R086 AS DTDEndTime,

dtdimports.R003 AS DTDDateOfWork,

gasandelecimport.R003 AS CateringDateOfWork,

gasandelecimport.R085 AS CateringStartTime,

gasandelecimport.R086 AS CateringEndTime,

dtdimports.JobSheet,

gasandelecimport.JobSheet AS JobSheet1,

(SELECT (TIMEDIFF(dtdimports.R086,dtdimports.R085))) AS `DTDTimeTaken`,

(SELECT (TIMEDIFF(gasandelecimport.R086,gasandelecimport.R085))) AS `CateringTimeTaken`,

(SELECT TIMEDIFF(dtdimports.R086,dtdimports.R085)) + TIMEDIFF(gasandelecimport.R086,gasandelecimport.R085) AS OverallTime

FROM account

INNER JOIN dtdimports ON account.EngineerId = dtdimports.userPK

INNER JOIN gasandelecimport ON account.EngineerId = gasandelecimport.userPK