I have a question and don’t know how to approach the problem exactly.
I have two tables as following:
Clients | c_id | name | reference | | ---- | ------- | --------- | | 1 | ClientA | 1 | | 2 | ClientB | 1 | | 3 | ClientC | 2 | | 4 | ClientD | 2 | | 5 | ClientE | 1 | | 1 | ClientF | 3 | Tour | t_id | name | count | | ---- | ------- | ----- | | 1 | TourA | 3 | | 2 | TourB | 2 | | 3 | TourC | 1 |
“Reference” in the “Client” table is defined as foreign key.
Is it possible to fill the column “count” in the table “Tour” with an automated formula where it counts how many times the t_id appears in the “Client” table?
Something like: COUNT(c_id) FROM clients WHERE reference = t_id
I have read about to create a view but not sure how to fetch the data correctly.
Thanks for your help, Raphael
UPDATE #1:
The workflow as described with the view works perfectly. I’m trying now to fill the column via a trigger but I’m getting an SQL error with the following code:
CREATE TRIGGER client_count AFTER UPDATE ON clients FOR EACH ROW SELECT t.*, ( SELECT COUNT(*) FROM clients c where c.tour_id = t.tour_id ) AS tours.tour_bookedspace FROM tours t
Advertisement
Answer
The view you have referred to is indeed the way to go here. The view you need to create needs to join the two tables and perform a count aggregation as follows:
CREATE VIEW vwTour AS SELECT t.t_id, t.name, COUNT(t.name) AS Cnt FROM tour t JOIN Clients c ON t.t_id = c.reference GROUP BY t_id, t.name