Selecting data for different columns from other same SQL Server table column

Question

I have a view named as This_View in the form:

Author  CoAuthor
----------------
677     901706
677     838459
677     901706
677     1695352
677     901706
1359    1695352
...  
...

I have to select Name from another table against column i.e. Author and CoAuthor where value of Name for both Author and CoAuthor exist in the same column of Other_Table.

I have tried this:

SELECT name as Author, name as CoAuthor
FROM Other_Table AA
JOIN This_View YA ON YA.Author = AA.aid AND YA.CoAuthor = AA.aid
ORDER BY Author

but not succeeded.

wiretext · Accepted Answer · 2016-02-04 05:13:13Z

1

you have to make different JOIN for Author and Coauthor

SELECT AA.name as Author, BB.name as CoAuthor
FROM This_View YA 
JOIN Other_Table AA ON YA.Author = AA.aid
JOIN Other_Table BB ON YA.CoAuthor = BB.aid
ORDER BY AA.name

answered Feb 4, 2016 at 5:13

wiretext

3,35216 silver badges19 bronze badges

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

2 Comments

wiretext Over a year ago

@Taufel Glad it helps you :)

maliks Over a year ago

Hmmm...I was aware of JOIN two or more tables but not aware of JOINing same table twice differently...anyhow a new curry to my menu :)

Collectives™ on Stack Overflow

Selecting data for different columns from other same SQL Server table column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related