I'm having some issue getting all of this to flow together.

I'm using Microsoft SQL Server.

I start with 3 tables.

Table #1 - Person

Table #2 - FoodTransaction

Table #3 - EntertainmentTransaction

From here my goal is to make a summary table or view including each person and the sum of their transactions by type of transaction.

To make the below view I do select with a series of sub selects.

• I start with a full outter join between the Person table and the FoodTransaction table. I create a new field sum(FoodTransaction.Cost) as FoodTotal.

• Then I do a left join between those results and the EntertainmentTransaction table where I create a new field sum(EntertainmentTransaction.Cost) as EntertainmentTotal.

• Then I add a where clause that says where FoodTotal is not null or EntertainmentTotal is not null. This where clause eliminates all entries from the Person table that have no transactions on either table.

I have successfully created the below table based on the above criteria.

View #1 - TransactionSummary

My final obstacle is below. I have an editable comment table which I'd like to join to the view.

Table #4 - Comment

The below View is the result of joining the Comment table back to View #1

View #2 - TransactionSummaryComment

This all works. The goal is then to display this view and allow users to edit the comment field.

The problem is if I go to edit one of the comments from within View #2 I get the following error:

"Cannot update the view or function because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator."

This leads me to think that what I'm doing won't work like this because of the aggregate fields which were created.

I imagine there must be a way to achieve the desired results different from the method I have tried.

Any thoughts or suggestions on a more efficient way of achieving this would be greatly appreciated.

CodePudding user response：

Glancing at the docs shows the following (https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver15)

Updatable Views

You can modify the data of an underlying base table through a view, as long as the following conditions are true:

Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following:

    An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.

    A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable.

The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.

TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.

With that being noted, the shown schema for Comment suggests that the Primary Key is CommentId and there is no unique constraint on PersonId.

This means that you could have multiple Comment records per PersonId. If this is the desired intent, then in order to keep them editable per the above restrictions we would not be allowed to group / aggregate them.

The following would allow you to edit them with the down side being that there could be multiple rows per PersonId if you have multiple comments.

CREATE VIEW dbo.TransactionSummaryComment
AS
SELECT p.PersonId
,f.Cost [FoodTotal]
,e.Cost [EntertainmentTotal]
,c.Comment
FROM Person p
LEFT JOIN (SELECT PersonId, SUM(Cost) [Cost] FROM FoodTransaction GROUP BY  PersonId) f ON p.PersonId = f.PersonId
LEFT JOIN (SELECT PersonId, SUM(Cost) [Cost] FROM EntertainmentTransaction GROUP BY PersonId) e ON p.PersonId = e.PersonId
LEFT JOIN Comment c ON c.PersonId = p.PersonId
WHERE f.Cost IS NOT NULL
OR e.Cost IS NOT NULL

If instead the intent is only have one comment per PersonId, namely the schema of Comment would have the Primary Key as PersonId, then we know that the comment row will only have a single record per Person and thus the view would not duplicate.