CODEWITHSUNDEEP
sql
user3657645
user3657645

Reputation: 11

Trying to INNER JOIN in SQL twice to one table

I have two tables, Flow and Users. Flow has a list of columns: Username1, Username2, Username3, etc., and some other data I'm fetching (DocumentNumber and Location).

Users has columns for the Username, FirstName, and LastName.

I want to be able to get the FirstName and LastName for all the usernames in Flow with a single query, as well as keep the DocumentNumber and Location. Is that possible?

Thank you for your time.

Upvotes: 0

Views: 57

Answers (3)

Janty
Janty

Reputation: 1714

SELECT 
USR.USERNAME AS USERNAME1,
USR1.USERNAME AS USERNAME1,
USR2.USERNAME AS USERNAME1,
FL.DOCUMENTNUMBER,
FL.LOCATION
 FROM
FLOW FL 
INNER JOIN USERS USR ON USR.USERID=FL.USERID
INNER JOIN USERS USR1 ON USR1.USERID=FL.USERID1
INNER JOIN USERS USR2 ON USR2.USERID=FL.USERID2

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

Yes, you can join to the same table multiple times, just give each JOIN a different alias:

...
FROM Flow f
LEFT JOIN Users u1 ON f.Username1=u1.Username
LEFT JOIN Users u2 ON f.Username2=u2.Username
LEFT JOIN Users u3 ON f.Username2=u3.Username
...

In your select list, refer to u1.FirstName for the first name associated with Username1, etc.

Upvotes: 1

StevieG
StevieG

Reputation: 8729

Yes, simply join to the users table multiple times:

SELECT 
  f.username,
  u1.firstname,
  u1.lastname,
  u2.firstname,
  u2.lastname,
  u3.firstname,
  u4.lastname,
  f.documentnumber,
  f.location
FROM flow f
INNER JOIN users u1 ON f.username1 = u1.username
INNER JOIN users u2 ON f.username2 = u2.username
INNER JOIN users u3 ON f.username3 = u3.username

Upvotes: 0

Related Questions