Retrieve all data from an inner join with duplicate column names in MySQL

Question

I have a database with tables for "users" and "profiles". Every profile links back to a user. I would like to do an inner join where I am able to retrieve a set of profiles that meet a condition along with the user data associated with them. However some columns in both profiles and users have the same names.

Currently I have a few methods I know I could do:

• I know I can do a select * on profiles and then programmatically for each profile also select * on users for the user of the profile.
• I could also do an alias for the columns with duplicate names but this would require maintenance if I were to update the columns in the future.
• I could also require every column to have a unique name, but that feels like not a good solution either.

Is there a better way? What is the best way to do this long term?

Per request, lets say the tables are defined as the following:

• Table users having columns of id, name
• Table profiles having columns of id, user_id, name

Answer 1

However some columns in both profiles and users have the same names.

Why do you think that would be a problem?
Specify the columns that you want to work with in every SQL query.
Never use select * in Production code.

To address your potential options:

... select * on profiles and then programmatically for each profile also select * on users ...

No, no, no and No.
This is a classic "1+N Query" Model and will perform atrociously badly as your table grows.
Just join the tables as you would do normally and give the columns useful aliases as you do so:

select 
  u.id as user_id 
, p.id as profile_id 
from users u 
inner join profiles p on u.id = p.u_id 
where ... 

... do an alias for the columns with duplicate names ... would require maintenance if I were to update the columns in the future.

... only if you change the columns that are involved in this query.
This is totally normal and you should expect to be doing this if your database is going to "be around" for any length of time.

... require every column to have a unique name ...

Actually, this is probably quite a Good Idea (making your data elements completely atomic) but it's [almost] completely unenforceable.
Databases are intrinsically shared entities and you will wind up with any number of people mucking about with any amount of stuff whenever they want/need to.

Answer 2

Normalization of table data suggests solutions to your problem.

1. It's best to not duplicate data across tables, especially if you plan to join these tables when querying in the first place. Therefore: Name should not exist in both tables. If you want the name of a user, grab the name from the table that has name/address/city/state/zip or whatever that base table holds rather than storing "Name" in several other tables. Helpful when you update: the name only exists in one place.

2. ID fields should not be "id", but "user_id" and "profile_id". This allows joining by that field name and the join being identical. If you standardize your naming convention for this, it can help you code faster in the future. eg: If you have a table named "addresses", the id for that table would logically be "addresses_id". Naming conventions can seem annoying, but they can help. And in this case that naming convention specifically would solve your collision issue. If two fields have the same "_id" name, they also have the same data.

3. I am morally opposed to admins telling "new programmers" in any field "don't do that" even though the system is specifically designed to ... "do that". Pulling all fields is not a bad thing. "SELECT * ..." has its uses when you add fields and do NOT want to have to add code to pull the new fields for reporting and exporting.

4. There may come a time when two tables MUST have the same field name because that's how life goes. In that case, when you have that collision, you can always add a prefix to them both to delineate the table in which they reside OR just add the prefix to the "newcomer". Ordinarily I like to name my fields fully, rather than abbreviating whenever possible which tends to cut down on those collisions (and is less cryptic five years later when modifying code that deal with that field directly, helps with memory).