CODEWITHSUNDEEP
mysqljoin
Mike Carpenter
Mike Carpenter

Reputation: 410

mySQL JOIN same table with specific columns

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.

Upvotes: 0

Views: 65

Answers (2)

Mike Carpenter
Mike Carpenter

Reputation: 410

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)

Upvotes: 0

Simone
Simone

Reputation: 656

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" ?

Upvotes: 2

Related Questions