This doesn't work

SELECT
_ID,
_Name,
_Type,
_Created,
(SELECT MAX(_Date) FROM CallStats WHERE _fkProjects = _ID) AS _LastCallDate,
(SELECT COUNT(*) FROM Calls WHERE _fkProjects = _ID) AS _Planned,
(SELECT COUNT(*) FROM Calls WHERE _LastResult IS NULL AND _fkProjects = _ID) AS _NoResult,
(SELECT COUNT(*) FROM Calls WHERE NOT _Done AND _fkProjects = _ID) AS _NotComplete,
(SELECT COUNT(*) FROM (SELECT DISTINCT _fkProjects,_fkCalls FROM CallStats ) T1 WHERE T1._fkProjects = _ID) AS _Called
FROM Projects

but this

SELECT
_ID,
_Name,
_Type,
_Created,
(SELECT MAX(_Date) FROM (SELECT _Date,_fkProjects FROM CallStats ) T1 WHERE T1._fkProjects = _ID) AS _LastCallDate,
(SELECT COUNT(*) FROM Calls WHERE _fkProjects = _ID) AS _Planned,
(SELECT COUNT(*) FROM Calls WHERE _LastResult IS NULL AND _fkProjects = _ID) AS _NoResult,
(SELECT COUNT(*) FROM Calls WHERE NOT _Done AND _fkProjects = _ID) AS _NotComplete,
(SELECT COUNT(*) FROM (SELECT DISTINCT _fkProjects,_fkCalls FROM CallStats ) T1 WHERE T1._fkProjects = _ID) AS _Called
FROM Projects

does. Its the first of the sub-selects that gives a problem - returns NULL in the first code.

Roy Lambert

Roy,

<< This doesn't work >>

Please send me the database catalog and tables that you're using via email.

I always need the data to test this stuff.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

OK I thought it might be WAD and I'd just get my poor old brain confused by your brilliant technical explanation

You already have the catalog and CallStats for the "b13 - Queries with subselects" email

Roy Lambert

Roy,

<< OK I thought it might be WAD and I'd just get my poor old brain confused
by your brilliant technical explanation >>

Sorry about the delay on this - I forgot to tell you the answer yesterday
when I figured it out finally:

This doesn't work

SELECT
_ID,
_Name,
_Type,
_Created,
(SELECT MAX(_Date) FROM CallStats WHERE _fkProjects = _ID) AS _LastCallDate,
FROM Projects

The problem here is the _ID reference - it should be this:

(SELECT MAX(_Date) FROM CallStats WHERE _fkProjects = Projects._ID) AS
_LastCallDate,

because CallStats also has an _ID column.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>because CallStats also has an _ID column.

I know my head isn't working to well right now but even so I should have spotted that. I think I've finally passed my shelf life. Victor Meldrew move over.

Roy Lambert