phpmyadmin Question posted in
The official documentation can be found here.

How to fill a SQL column with data (calculated) from another table – Phpmyadmin

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
Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    No you can’t. Generated columns can only use data from the same table.

    The options you have are:

    1. Use a view

    You can select from a view that computes the extra value(s) you want. For example:

    create view tour_data as
select t.*,
  (
    select count(*) from clients c where c.reference = t.t_id
  ) as number_of_clients
from your t

    2. Use a trigger

    Alternatively, you can add the extra column number_of_clients and populate it using a trigger every time a row is added, modified, or deleted from the table clients.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search