I was wondering if it was possible to populate a computed/generated column using the foreign key to get information from the linked cell/table
I have a relationship set up between TableA and TableB.
TableA is where I enter all of the job information and TableB gives the name of all workers, so when a job is entered,the worker name is added to the app from TableB via the foreign key.
the problem is though, via the app, when I search via the workers name, it does not give any results, instead I need to search via the Foreign Key number which is the ID from TableB
So TableA looks like
Name | fk_TableA_TableB |
---|---|
Job 1 | 2 |
Job 2 | 1 |
TableB
id | Name |
---|---|
1 | Joe |
2 | John |
The Result I would linke to acheive is an extra row at the end of TableA
which gives the value of TableB based on the foreign key
Name | fk_TableA_TableB | TableB |
---|---|---|
Job 1 | 2 | John |
Job 2 | 1 | Joe |
I am looking for an expression that will allow me to acheive this using a generated/computed column
I have tried using an SQL Expression in HeidiSQL
SELECT 'Name' from TableB where id = fk_TableA_TableB
But no joy
Everything I see on this subject talks about using joins
However, the tables are constantly updated and I need a solution that will automatically pull the name from TableB when a new entry is input in TableA
VIEW
?views
not having a primary key, I would not be able to reference anything from it.