sql - How to build virtual columns?

Question

Sorry if this is a basic question. I'm fairly new to SQL, so I guess I'm just missing the name of the concept to search for.

Quick overview.

First table (items):

ID     | name
-------------
1      | abc
2      | def
3      | ghi
4      | jkl

Second table (pairs):

ID     | FirstMember   | SecondMember         Virtual column (pair name)
-------------------------------------
1      | 2             | 3                    defghi
2      | 1             | 4                    abcjkl

I'm trying to build the virtual column shown in the second table It could be built at the time any entry is made in the second table, but if done that way, the data in that column would get wrong any time one of the items in the first table is renamed.

I also understand that I can build that column any time I need it (in either plain requests or stored procedures), but that would lead to code duplication, since the second table can be involved in multiple different requests.

So is there a way to define a "virtual" column, that could be accessed as a normal column, but whose content is built dynamically?

Thanks.

Edit: this is on MsSql 2008, but an engine-agnostic solution would be preferred.

Edit: the example above was oversimplified in multiple ways - the major one being that the virtual column content isn't a straight concatenation of both names, but something more complex, depending on the content of columns I didn't described. Still, you've provided multiple paths that seems promising - I'll be back. Thanks.

Answer 1

You need to join the items table twice:

select p.id,
       p.firstMember,
       p.secondMember,
       i1.name||i2.name as pair_name
from pairs as p
  join items as i1 on p.FirstMember = i1.id
  join items as i2 on p.SecondMember = i2.id;

Then put this into a view and you have your "virtual column". You would simply query the view instead of the actual pairs table wherever you need the pair_name column.

Note that the above uses inner joins, if your "FirstMember" and "SecondMember" columns might be null, you probably want to use an outer join instead.