"= ALL (SELECT n FROM ...)" doesn't work.

[samshiell]

Hi
I'm trying to filter a table on where one of the column values matches ALL values in sub table... this is wot I'm doing :=

SELECT * FROM tblTasks tsk
WHERE MeasureID = ALL (SELECT MeasureID
FROM tblKeyTasks
WHERE type = tsk.Type)

It doesn't work. If I change the = to <> then it returns all the rows that aren't in the sub table, but I cannot get it to retun those that are there. Still doesn't perform as expected if I take the WHERE clause out.

Am I missing something obvious here? If not has anyone an alternative solution?

To clarify, I need to return only those rows that match ALL values (of the correct type) in tblKeyTasks

Cheers

Sam

 

[nabiS2K]

I think you mean to do this...

SELECT * FROM tblTasks tsk
WHERE MeasureID IN (SELECT MeasureID
                       FROM tblKeyTasks
                       WHERE   type = tsk.Type)

 

[samshiell]

Hi

Thanks for your suggestions but this will give an "any" match, which isn't what I want

I think I probably haven't explained what I'm trying to achieve.

People do tasks and each one gets entered into tblTasks.

People also have one jobtype, called "Type" which (for the sake of this example) is also saved in tblTasks.

Each type has some tasks that must be done. So, I want to list the rows in tblTasks what match EVERY row in tblKeyTasks, for the relevant jobtype.

Any ideas how I could achieve that?

Sam

 

[amorous]

Try this:

SELECT * FROM tblTasks tsk
WHERE MeasureID IN (SELECT MeasureID
FROM tblKeyTasks
WHERE type = tsk.Type)

-VJ

 

[nigelrivett]

Something like

SELECT *
FROM tblTasks tsk
where not exists
(select *
from tblKeyTasks kt
left join tblTasks tsk2
on kt.MeasureID = tsk2.MeasureID
and tsk2.type = tsk.Type
where kt.type = tsk.Type
and tsk2.MeasureID is null
)

That'll give all entries in tblKeyTasks which have all MeasureID for that Type from tblKeyTasks.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[samshiell]

Hi

Thanks for that. What you had wasn't exactly right as it still returned someone who had not all tasks, but the following works....

SELECT * FROM tblTasks tsk
WHERE NOT EXISTS (
SELECT *
FROM tblKeyTasks kt
LEFT OUTER JOIN tblTasks tsk2
ON tsk2.MeasureID = kt.measureID
WHERE tsk2.MeasureID IS NULL
AND kt.Type = tsk.Type)

.... but you got me on the right track so thanks for your prompt.


Sam