How to remove duplicate user_id from a query result?

Question

The problem is I am trying to use Create View for a query but I keep getting an error which means That I have a duplicate row

My two table structure and relation

My query

SELECT * FROM user_profile as A
INNER JOIN user_personal_info as B 
on A.user_id=B.user_id

Results includes two user_id rows and I need B.user_id to be removed to be able to use it like that

CREATE View Studentinfo as
SELECT * FROM user_profile as A
INNER JOIN user_personal_info as B 
on A.user_id=B.user_id

mysql error #1060 - Duplicate column name 'user_id'

note: I don't want to name each row by its name and exclude B.user_id as I might add more rows and by that the query would be very long.

Answer 1

You can do this by using the USING clause instead of ON:

CREATE VIEW Studentinfo AS
SELECT * 
FROM user_profile AS A INNER JOIN user_personal_info AS B 
USING(user_id);

This way the column user_id is returned only once.

Note that if you add more columns to the tables (as you say that you intent to) you should take special care so that there is no common column name.