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.

Why should I not upload images of code/data/errors when asking a question? An ERD is an image of DDL. Debug questions require a minimal reproducible example. How to Ask Help center — philipxy
– philipxy, Commented Nov 3, 2022 at 17:07
This is a faq. Please before considering posting: Pin down code issues via minimal reproducible example. Read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at Meta Stack Overflow & Meta Stack Exchange. How much research effort is expected of Stack Overflow users? How to Ask Help center — philipxy
– philipxy, Commented Nov 3, 2022 at 17:08

forpas · Accepted Answer · 2022-11-03 16:59:34Z

0

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.

answered Nov 3, 2022 at 16:59

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1