mySQL JOIN same table with specific columns

Question

I need to fetch all entries from a table titled users and JOIN a user from the same table based on a userID in the original users entry . I have a simple JOIN query that completes this requirement but I only want to return two columns (userID and fullName) during the JOIN. Currently I'm returning the whole user entry from the JOIN and obviously the column names overlap. For our application's purposes, I need to rename the columns returned from the JOIN. I'm currently using the query below.

SELECT * FROM users u1

JOIN users AS u2
ON(u1.dealer = u2.userID)

This seems like it should be relatively simple but I can't seem to figure it out. I've searched for hours but haven't found a clear solution.

Like this SELECT u1.*, u2.userID as dealer_id, u2.fullName as dealer_name ? — M Khalid Junaid
– M Khalid Junaid, Commented Jun 16, 2018 at 4:14
Your select statement seems to reference two tables that don't exist based on my question. u1 and u2 alias aren't defined in your answer and there's no explanation for the join statement. Down vote for not fully reading the question — Mike Carpenter
– Mike Carpenter, Commented Jun 16, 2018 at 4:28
Read it again select statement reference to aliases defined in your query u1 for first user table in from clause and u2 for second user table in join part, Also its a comment not full answer, If it was i had posted it as an answer, Hope that makes sense — M Khalid Junaid
– M Khalid Junaid, Commented Jun 16, 2018 at 13:30

Simone · Accepted Answer · 2018-06-16 11:23:19Z

2

SELECT u1.userID as userID,u1.fullName as fullName, 
u2.userID as dealeruserID,u2.fullName as dealerfullName 
 FROM users u1 JOIN users AS u2 ON(u1.dealer = u2.userID)

I am assuming you need user data and their corresponding Dealers.

Just a thought - for the dealer users - what is their value of "dealer" ?

answered Jun 16, 2018 at 11:23

Simone

6762 gold badges8 silver badges27 bronze badges

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

1 Comment

Mike Carpenter Over a year ago

This is a lot closer to what I was thinking. Only issue is that I need the entire entry for u1 and userID and fullName for u2. Any way to accomplish that without specifying every column manually? Also, the dealer column is an integer.

Mike Carpenter · Accepted Answer · 2018-06-16 14:20:45Z

0

With the help from Simone I was able to modify her answer to fit my use case.

SELECT u1.*, 
u2.userID as dealeruserID,
u2.fullName as dealerfullName 
FROM users u1 

JOIN users AS u2 ON(u1.dealer = u2.userID)

answered Jun 16, 2018 at 14:20

Mike Carpenter

4104 silver badges12 bronze badges

2 Comments

M Khalid Junaid Over a year ago

This looks identical to what I stated at first glance, And by the way you should have accepted her answer instead of posting this

Mike Carpenter Over a year ago

Your answer was incomplete. Her’s was complete. I have already accepted her answer

Collectives™ on Stack Overflow

mySQL JOIN same table with specific columns

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related