Using "SELECT COUNT"

[lestermadoc]

I have a one to many relationship between "cases" and "casenotes". Wherever there "casenotes.flagger = true" I want to mark "cases.manflag = true"

My problem comes when I want to mark "cases.manflag = false" where there are no instances of "casenotes.flagger = true".

what code must I use to perform this operation? )I am told the "select count" tool would work, but I do not know how to use it.)

 

[balves]

You don't need SELECT Count. Assuming you have a primary key on cases (say cases.caseid) that is a foreign key in casenotes:

UPDATE
SET cases.manflag = false
FROM cases LEFT JOIN casenotes
ON cases.caseid = casenotes.caseid
WHERE casenotes.caseid IS NULL

 

[lestermadoc]

Well, cases always have notes, regardless of whether or not casenotes.flagger = true.

 

[tlbroadbent]

T-SQL has EXISTS and NOT EXISTS conditions which you can use in this situation.

Update cases Set ManFlag = 0
Where Not Exists
(Select *
From casenotes
Where caseid = cases.caseid
And Flagger = 1) Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm