Hello dear readers,
I have 2 tables, let's say
first table: "Appointmen t", which has 3 fields (I've simplified the schema for better understanding)
The second table is called "Task" and has 2 fields (again, simplified schema)
Now the problem:
In a kind of "Tasks to plane view", I'm pretty sure I can speed things up since I recall a Sum on each criteria .. I'v tried to use alias, but propably missed something because I only get error message on return ("Invalid Column name").
Here is my query[code=mysql]
SELECT Task.ID, Task.RequiredLe ngth,
(SELECT SUM(Length) AS Expr1
FROM Appointment
WHERE (TaskID = Task.ID)
GROUP BY TaskID) AS PlannedLength
FROM Task
WHERE ((SELECT SUM(Length) AS Expr1
FROM Appointment AS Appointment_1
WHERE (TaskID = Task.ID)
GROUP BY TaskID) < RequiredLength)
OR ((SELECT SUM(Length) AS Expr1
FROM Appointment AS Appointment_1
WHERE (TaskID = Task.ID)
GROUP BY TaskID) ) IS NULL
I have tried the following query, but it does not work :
SELECT Task.ID, Task.RequiredLe ngth,
(SELECT SUM(Length) AS Expr1
FROM Appointment
WHERE (TaskID = Task.ID)
GROUP BY TaskID) AS PlannedLength
FROM Task
WHERE ((PlannedLength < RequiredLength) OR (PlannedLength IS NULL))[/code]
'************** *************** *************** *************** ******
Any help would be greatly appreciated, I'm a real newbie :
Thank for your answers,
Happy coding
I have 2 tables, let's say
first table: "Appointmen t", which has 3 fields (I've simplified the schema for better understanding)
ID as GUID
(the key)Length as Float
TaskID as GUID
((external key)The second table is called "Task" and has 2 fields (again, simplified schema)
ID as GUID (key)
RequiredLength as Float
Now the problem:
In a kind of "Tasks to plane view", I'm pretty sure I can speed things up since I recall a Sum on each criteria .. I'v tried to use alias, but propably missed something because I only get error message on return ("Invalid Column name").
Here is my query[code=mysql]
SELECT Task.ID, Task.RequiredLe ngth,
(SELECT SUM(Length) AS Expr1
FROM Appointment
WHERE (TaskID = Task.ID)
GROUP BY TaskID) AS PlannedLength
FROM Task
WHERE ((SELECT SUM(Length) AS Expr1
FROM Appointment AS Appointment_1
WHERE (TaskID = Task.ID)
GROUP BY TaskID) < RequiredLength)
OR ((SELECT SUM(Length) AS Expr1
FROM Appointment AS Appointment_1
WHERE (TaskID = Task.ID)
GROUP BY TaskID) ) IS NULL
I have tried the following query, but it does not work :
SELECT Task.ID, Task.RequiredLe ngth,
(SELECT SUM(Length) AS Expr1
FROM Appointment
WHERE (TaskID = Task.ID)
GROUP BY TaskID) AS PlannedLength
FROM Task
WHERE ((PlannedLength < RequiredLength) OR (PlannedLength IS NULL))[/code]
'************** *************** *************** *************** ******
Any help would be greatly appreciated, I'm a real newbie :
Thank for your answers,
Happy coding
Comment