CODEWITHSUNDEEP
sqlsql-server
Reiqen
Reiqen

Reputation: 53

SQL Server: same column but different values

I have two tables: Users and Messages.

I try to write a proper select sentence, it does look like this for now:

SELECT
  Messages.Message_id,
  Messages.From_id,
  Users.Username,
  Messages.To_id,
  Users.Username,
  Messages.Date,
  Messages.Subject,
  Messages.Text,
  Messages.Picture
FROM
  Messages
INNER JOIN
  Users
ON Messages.From_id = Users.User_id
AND Messages.To_id = Users.User_id

I worry about Users.Username that repeats two times and I am not sure that this will show me a proper result.

Please help me to create a proper select sentence.

Upvotes: 1

Views: 45

Answers (2)

Abishake Ryan
Abishake Ryan

Reputation: 31

Try the below query,

SELECT
Messages.Message_id,
Messages.From_id,
FromUser.Username,
Messages.To_id,
ToUser.Username,
Messages.Date,
Messages.Subject,
Messages.Text,
Messages.Picture
FROM
Messages
INNER JOIN
Users FromUser on Messages.From_id = FromUser.User_Id
INNER JOIN
Users ToUser on Messages.To_id = ToUser.User_Id

Upvotes: 0

TomTom
TomTom

Reputation: 62159

It is not different values. There is no join defined so it will fall down to ONE join condition, outputting the values two times. Done. If anything, this is something a code review would flag as obviously wrong - it is possible you want to show users from and to, in which case you need:

  • Join TWO times (i.e. TWO inner join statements)
  • Both joins having other name aliases (i.e. AS Users_To and As Users_From AND
  • obviously getting the name from both aliases and not the table name.

Upvotes: 1

Related Questions