Reputation: 410
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
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
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