display column alias from other tbl

Question

I have 2 tables tbl1 and tbl2 tbl1 has userid col1 col2 col3 (multiple records per user) EDIT tbl2 has userid col4 col5 col6 (Single record per user) On my original post I had col3 listed here as well which was accident.

tbl2 is used to store custom column names for each user.

now I need to know how do I display this custom column name on results. ie how do I reference alias to pull this custom column name for each user.

SELECT col1 AS (Dont know what to put here...)

Something like ...col1 AS tbl2.col3 where userid = "testuser" How can I accomplish this? Maybe somekind of outer/inner query???

Thanks

Answer 1

The correct syntax is:

SELECT tbl2.userid, tb2.col3 AS [your name here], tbl1.col1 AS [your name here], ...
FROM tbl2
INNER JOIN tbl1 ON tbl1.userid = tb2.userid
WHERE tbl2.userid = "testuser"

The columns themselves are prefixed with the table name (not the alias). This is only truly necessary when two different tables share a column name, but it's not a bad habit to pick up even in simpler cases.

Answer 2

Present on the presentation layer, not the database side:

select
    a.userid,
    col1,
    col2,
    a.col3,
    b.col3 as user_col1,
    b.col4 as user_col2,
    b.col5 as user_col3
from
    tblA a
    inner join tblB b on
        a.userid = b.userid
where
    userid='testuser'

Then, on the application side, just use col4, col5, and col6 as the column names once you go to display the records.

Answer 3

Do something like this:

SELECT tbl1.col1 as tbl1_col1,tbl2.userid as tbl2_usrid, tbl2.col3 as tbl2_usrid_custcol
FROM tbl1,tbl2
WHERE tbl1.userid = tbl2.userid;

Answer 4

This has to be done either as dynamic SQL (not ideal) or at the presentation layer (greatly preferred).