how do i automatically fill in fields using data from another table (based on foreign key = primary-CodePudding

i'm currently a student working on a comp sci project. basically, i have a main client table with primary key "Client_ID." within this client table, there are other fields such as "Client_Name", "Phone_Number." And I have other tables such as quotation, where there is also the "Client_ID" field as the foreign key, referencing the pk in the client table. Within the quotation table, there are also a few selected duplicate fields from the client table such as Client_Name again. I do this so I can make automatically generated quotation documents using fields from the quotation table. Anyhow, is there a way so that when I type in for example, "C-0001" in the foreign key client_id in the quotation table, there is no need to type in fields such as the Client_Name again, and instead corresponding data is copied over from the client table?

i've been stuck on this for a good while and my cs teacher sucks, so help would be really much appreciated.

CodePudding user response：

This could be done with a trigger, but the more idiomatic approach would be to keep these fields in the client table only, and use a join to query them when you need to create a quotation report:

SELECT q.*, c.client_name, c.phone_number
FROM   quotation q
JOIN   client c ON q.client_id = c.client_id