Getting multiple columns from another table

[GGOULET-DJL]

I'm trying to figure out how to get multiple data point from the same row in another table based on one selection.

For example, if I do a FULL match from systems.system_id to subsystems.system_id.

I would like to have systems.system_name to row match systems.system_id and autofill subsystems.system_name based on subsystems.system_id.

Is that possible, how do I do that?

Thank you

[pawelsalawa]

UPDATE subsystem sub
   SET system_name = sys.system_name
  FROM system sys
 WHERE sys.system_id = sub.system_id

[GGOULET-DJL]

I have a bit of trouble understanding if I have to add that in one of the constraints field or if I need to execute a query manually. Thank you

[pawelsalawa]

The example I gave is to execute it once and get the data updated, but if you expect to insert new rows into system and/or subsystem, then you will have to update new rows respectively, but this is against usual approach of working with relational databases. Instead of maintaining (updating) the same value in two tables, you should just use joined queries to get system name from system table, even if you select all other data from sybsystem:

SELECT sys.system_name, subsystem.* FROM subsystem sub JOIN system sys ON (sys.system_id = sub.system_id)

[GGOULET-DJL]

ok i understand, would it be bad practice to do sys.system_name and sub.system_name instead of linking them by id (sys.system_id = sub.system_id)?

The reason I would do it that way is to be able to see the name of the system as I add new subsystems.

My concern is getting lost in ID's linking and what they represent, i'm not sure how to approach that. Very helpful thank you

[pawelsalawa]

A well formed relational database do not hold redundant entries. If subsystem.system_name will always be same as system.system_name, then you should rely on relation system.system_id = subsystem.system_id and read the system_name from system table.

Nobody forbids you do have redundant columns on both tables. It just brings new problems - just like the one that you asked here originally.

[GGOULET-DJL]

understood, I'm just starting to optimise my databases. I guess my question is how you keep track of what you're linking when you do ID linking? as it gets bigger I imagine all the ID gets hard to keep track off, do you have any wisdom on that? thank you