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.

Sounds like you want a trigger...or a view...or maybe just a query... I suppose it depends on your use case :P — Charleh, Jul 30 '13 at 13:56
ooo good article... https://en.wikipedia.org/wiki/View_(SQL) — obimod, Jul 30 '13 at 13:58
Guilty of oversimplifying the example! Sorry all. (I'm editing this... hold on...) — jmr, Jul 30 '13 at 14:00

score 5 · Accepted Answer · answered Jul 30 '13 at 13:54

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.

Accepted as correct answer, since you were the first to suggest using views. I ended up using a view and a scalar-valued function (to build the name) - it works, but performance isn't acceptable - but this would be a separate question. Thanks. — jmr, Jul 30 '13 at 19:39

score 2 · Answer 2 · answered Jul 30 '13 at 13:57

You can use a view, which creates a table-like object from a query result, such as the one with a_horse_with_no_name provided.

CREATE VIEW pair_names AS
SELECT p.id,
    p.firstMember,
    p.secondMember,
    CONCAT(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 to query the results just do:

SELECT id, pair_name FROM pair_names;

score 0 · Answer 3 · answered Jul 30 '13 at 14:00

You could create a view for your 'virtual column', if you wanted to, like so:

CREATE VIEW aView AS

SELECT
    p.ID,
    p.FirstMember,
    p.SecondMember,
    a.name + b.name as 'PairName'
FROM
    pairs p
LEFT JOIN
    items a
ON
    p.FirstMember = a.ID
LEFT JOIN
    items b
ON
    p.SecondMember = b.ID

Edit:

Or, of course, you could just use a similar select statement every time.

score 0 · Answer 4 · answered Jul 30 '13 at 14:08

When selecting from tables you can name the results of a column using AS.

SELECT st.ID, st.FirstMember, st.SecondMember, ft1.Name + ft2.Name AS PairName
FROM Second_Table st 
JOIN First_Table ft1 ON st.FirstMember = ft1.ID
JOIN First_Table ft2 ON st.SecondMember = ft2.ID

Should give you something like what you are after.

How to build virtual columns?

4 Answers4

Linked