Is there a way to rename a similarly named column from two tables when performing a join?

Question

I have two tables that I am joining with the following query...

select * 
  from Partners p 
inner join OrganizationMembers om on p.ParID = om.OrganizationId 
where om.EmailAddress = '[email protected]' 
  and om.deleted = 0

Which works great but some of the columns from Partners I want to be replaced with similarly named columns from OrganizationMembers. The number of columns I want to replace in the joined table are very few, shouldn't be more than 3.

It is possible to get the result I want by selectively choosing the columns I want in the resulting join like so...

select om.MemberID, 
       p.ParID, 
       p.Levelz, 
       p.encryptedSecureToken, 
       p.PartnerGroupName, 
       om.EmailAddress, 
       om.FirstName, 
       om.LastName 
 from Partners p 
inner join OrganizationMembers om on p.ParID = om.OrganizationId 
     where om.EmailAddress = '[email protected]' 
       and om.deleted = 0

But this creates a very long sequence of select p.a, p.b, p.c, p.d, ... etc ... which I am trying to avoid.

In summary I am trying to get several columns from the Partners table and up to 3 columns from the OrganizationMembers table without having a long column specification sequence at the beginning of the query. Is it possible or am I just dreaming?

Answer 1

select om.MemberID as mem

Use th AS keyword. This is called aliasing.

Answer 2

Try this:

p.*,
om.EmailAddress, 
om.FirstName, 
om.LastName 

You should never use * though. Always specifying the columns you actually need makes it easier to find out what happens.

Answer 3

You are dreaming in your implementation.

Also, as a best practice, select * is something that is typically frowned upon by DBA's.

If you want to limit the results or change anything you must explicitly name the results, as a potential "stop gap you could do something like this.

SELECT p.*, om.MemberId, etc..

But this ONLY works if you want ALL columns from the first table, and then selected items.

Answer 4

But this creates a very long sequence of select p.a, p.b, p.c, p.d, ... etc ... which I am trying to avoid.

Don't avoid it. Embrace it!

There are lots of reasons why it's best practice to explicity list the desired columns.

• It's easier to do searches for where a particular column is being used.
• The behavior of the query is more obvious to someone who is trying to maintain it.
• Adding a column to the table won't automatically change the behavior of your query.
• Removing a column from the table will break your query earlier, making bugs appear closer to the source, and easier to find and fix.

And anything that uses the query is going to have to list all the columns anyway, so there's no point being lazy about it!