Dear Friends,

Query for that wiil read the school12 table and tally the number of schools/district. In addition, If any school in a district has FAIL score, then the status of the distict is FAIL. populate the district table with the results.
populate the dist12 table with the results.

Thanks in advance..

I changed the sid data in the school12 table so that we can join cleanly to the school12 table.

INSERT INTO school12 VALUES(1, 123, 'ABC','FAIL')
INSERT INTO school12 VALUES(2, 123, 'ABC1','PASS')
INSERT INTO school12 VALUES(3, 125, 'XYZ', 'PASS')
INSERT INTO school12 VALUES(4, 125, 'PQR','PASS')
INSERT INTO school12 VALUES(5, 126, 'STU', 'PASS')

One possible solution...
INSERT INTO dist12
SELECT a.did, SchoolCount, ISNULL(c.score, 'PASS')
FROM (SELECT did FROM school12 GROUP BY did) a
    JOIN (SELECT did, COUNT(sid) SchoolCount FROM school12 GROUP BY did) b ON a.did = b.did
    LEFT JOIN (SELECT did, score FROM school12 WHERE score = 'FAIL') c ON a.did = c.did

Make sure you list me on your homework as a reference... LOL


Adam Gossage
Lake Wylie, SC, USA

Dear agossage
I really appreciate your quick response.Thanks a lot. I 'll get back to you If I need more Information.