Here is my existing schema. I would like to change this and remove the "NumDefects" from the [Analysis] table. To do this I need to update the [vwAnalysis] view to determine the number of defects from the [Defects] table by the AnalysisID. I can't seem to get the right select for the new version of the view. CREATE TABLE [Analyzers] (AnalyzerID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,Name VARCHAR NOT NULL UNIQUE ); CREATE TABLE [Analysis] (AnalysisID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,AnalyzerID INTEGER NOT NULL REFERENCES [Analyzers] DEFERRABLE INITIALLY DEFERRED ,ScanID INTEGER NOT NULL REFERENCES [Scans] DEFERRABLE INITIALLY DEFERRED ,Timestamp DATETIME NOT NULL ,EndTime DATETIME NOT NULL DEFAULT CURRENT_TIME ,NumDefects INTEGER DEFAULT 0 ,Result VARCHAR ); CREATE TABLE [Defects] (DefectID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,ImageID INTEGER NOT NULL REFERENCES [Images] DEFERRABLE INITIALLY DEFERRED ,AnalysisID INTEGER NOT NULL REFERENCES [Analysis] DEFERRABLE INITIALLY DEFERRED ,X REAL NOT NULL DEFAULT 0.0 ,Y REAL NOT NULL DEFAULT 0.0 ,W REAL NOT NULL DEFAULT 0.0 ,H REAL NOT NULL DEFAULT 0.0 ); CREATE VIEW [vwAnalysis] AS SELECT [Analyzers].[AnalyzerID] , [Analyzers].[Name] AS [Analyzer] , [Analysis].[AnalysisID] , [Analysis].[ScanID] , [Analysis].[Timestamp] , [Analysis].[EndTime] , [Analysis].[NumDefects] , [Analysis].[Result] FROM [Analysis] JOIN [Analyzers] ON [Analyzers].[AnalyzerID] = [Analysis].[AnalyzerID] ORDER BY [Analysis].[Timestamp];
This is what I would like to do but I do not understand how to make the JOIN correctly. SELECT [Analyzers].[AnalyzerID] , [Analyzers].[Name] AS [Analyzer] , [Analysis].[AnalysisID] , [Analysis].[ScanID] , [Analysis].[Timestamp] , [Analysis].[EndTime] , COUNT(DefectID) AS NumDefects <<== count of Defects that match the AnalysisID , [Analysis].[Result] FROM [Analysis] JOIN [Analyzers] ON [Analyzers].[AnalyzerID] = [Analysis].[AnalyzerID] ORDER BY [Analysis].[Timestamp]; Thanks in advance for your help with this. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
