Alias 2 columns to the same name

Question

I am wondering if it is possible to alias 2 columns from 2 separate tables to the same name.

For example i want to join an email table with a user table and admin table and let the respective 'name' columns be combined in the same column.

Email Table: Email ID | Email Address

User Table: Username| Email ID

Admin Table: Username| Email ID

Result:

Username | Email Address

So you want these columns to be separate, but to have the same name, or did you want them fully merged as well? — RonaldBarzell, Nov 28 '12 at 00:20
@user1161318 Fully merged, it seems like UNION is the magic keyword that i was looking for. — Klathmon, Nov 28 '12 at 00:28

score 2 · Accepted Answer · answered Nov 28 '12 at 00:25

I believe this is what you're trying to get:

SELECT a.Username, a.Email_ID, b.Email_Address
FROM
    (SELECT Username, Email_ID FROM User
    UNION ALL
    SELECT Username, Email_ID FROM Admin) a
INNER JOIN Email b ON b.Email_ID = a.Email_ID

This will stack the User and Admin tables and then JOIN the Email table.

Note that if there are duplicates and you want to remove them you should use UNION instead of UNION ALL.

score 1 · Answer 2 · answered Nov 28 '12 at 00:25

1

Assuming that your tables have the following columns

Email: EmailId, EmailAddress
User:  UserName, EmailId
Admin: UserName, EmailId

Then The following union should get them into one list. Note I'm using a LEFT JOIN to allow for the cases where EmailId in the User or Admin tables is invalid

SELECT  u.UserName, e.EmailAddress
FROM USER u
LEFT JOIN Email e ON e.Id = u.EmailId

UNION

SELECT  a.UserName, e.EmailAddress
FROM Admin u
LEFT JOIN Email e ON e.Id = a.EmailId

answered Nov 28 '12 at 00:25

Preet Sangha

64,563
18
145
216

The difference between mine and @njk's answer is that I do the joins first and union the result, while he unions the User and Admin tables first and then joins. It's valid either way, but I suspect his might be more optimal – Preet Sangha Nov 28 '12 at 00:28
Technically, you're performing two `JOIN`s which depending on the records, may take longer. – Kermit Nov 28 '12 at 00:31
This was a very simplified example, so I'll be reading up on UNION so i can make an efficient query – Klathmon Nov 28 '12 at 00:32
@njk Yes - I agree but it's only one statement overall so possibly the optimiser may be kind. But I think your approach is sounder. – Preet Sangha Nov 28 '12 at 00:33

Alias 2 columns to the same name

2 Answers2