Creating a very specific column in HeidiSQL

Question

I've been trying to set up a small database with HeidiSQL but I've stumbled across a problem. I want to add in a column in a certain table that counts the amount of columns in another table containing a certain value. I've tried it by setting a default value, entering a custom value looking like this

    SELECT COUNT(LidID) AS AantalSchepen FROM SCHIP WHERE SCHIP.LidID=LID.LidID

Whereas LidID is the name of the column I want to count the number of rows, containing the same value in table SCHIP as in table LID. The value of LidID is the primary key of table LID, and therefor logically the foreign key in table SCHIP.

However, when I enter this statement in the custom default value field, it tells me the default value is invalid. Is what I'm doing completely impossible, or is my SQL statement flawed?

Edit: The purpose of the database is to be the source of info shown in a table on a website. As I've read in previous responses, what I'm trying to do here is simply impossible, so a different solution would be... ?

if you meant rows, then i guess you are missing the GROUP BY statement — Asped, Apr 24 '15 at 12:30
@jarlh yeah I guess that's what I meant, english is not my native language so I might've messed up some grammer in my post, will edit it. — Ciphra, Apr 25 '15 at 13:35
@Asped I'm just listing the number of rows that contain the value equal to the one I select, instead of listing all the different values and their corresponding amount of instances. — Ciphra, Apr 25 '15 at 13:38

score 0 · Answer 1 · answered Apr 24 '15 at 12:46

In general, you cannot add a default value that "runs a query". This may depend on the database, but I can't think of a database that supports this.

What you can do is access the data using a view:

create view v_lid as
    select l.*,
           (select count(*)
            from SCHIP s
            where s.LidID = l.LidID
           ) as AantalSchepen
    from lid l;

Then, when you access the view, you will get the latest value for this.

Note: this is a bit different from a "default" value, which would put in the value when the row is created. If you really want that, you will need a trigger. Or, perhaps, there is some date logic that would arrive at the same value.

hmmm ok, let me elaborate on the purpose of the row in the main thread, second. — Ciphra, Apr 25 '15 at 13:28

score 0 · Answer 2 · answered Dec 10 '15 at 09:50

In MySQL 5.7.6 and MariaDB 5.2 upwards, a table can have virtual columns. MariaDB calls it virtual, in MySQL it's called generated. Both mean the same, and also the syntax is quite similar. HeidiSQL's table editor supports creating such virtual columns. Example CREATE CODE:

CREATE TABLE table1 (
 a INT NOT NULL,
 b VARCHAR(32),
 c INT AS (a mod 10) VIRTUAL
)

Unfortunately, virtual columns do not support subqueries, which is why that all does not help for a COUNT(*) query.

Creating a very specific column in HeidiSQL

2 Answers2