SQL Server: same column but different values

Question

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.

TomTom · Accepted Answer · 2020-02-29 13:24:55Z

1

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.

answered Feb 29, 2020 at 13:24

TomTom

62.4k11 gold badges95 silver badges152 bronze badges

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

2 Comments

Reiqen Over a year ago

Is that the correct one?: ``` SELECT Messages.Message_id, Messages.From_id, UsersFrom.Username, Messages.To_id, UsersTo.Username, Messages.Date, Messages.Subject, Messages.Text, Messages.Picture FROM Messages INNER JOIN Users AS UsersFrom ON Messages.From_id = UsersFrom.User_id INNER JOIN Users AS UsersTo ON Messages.To_id = UsersTo.User_id ``` Unfortunately can't test it right now, I just need to know if the way I think is correct.

TomTom Over a year ago

Exactly. I generally NEVER use implicit joins - too much danger in them - and always define the join condition.

Abishake Ryan · Accepted Answer · 2020-02-29 17:31:09Z

0

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

answered Feb 29, 2020 at 17:31

Abishake Ryan

318 bronze badges

Collectives™ on Stack Overflow

SQL Server: same column but different values

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related