join tables in sql server

Question

i have 2 tables base: Id int Name Varchar and values in base: 1 abc 2 cde 3 def Sub: sid int CreatedBy Id(From base table Id) ModiFiedBy Id(From base table Id) and values in sub 1 2 3 2 1 2 3 3 2 now i want table like this sid CreatedBy CreatedByName ModifiedBy ModifiedByName 1 2 cde 3 def 2 1 abc 2 cde 3 3 def 2 cde how to write this query please help me

Tapan Patel · Answer

Select sidcreatedby t1.name  modifiedby  t2.name From sub Left outer join base t1 on sub.createdby = t1.Id Left outer join base t2 on sub.modifiedby = t2.Id Also make sure to include With NOLOCK.

Delpin Susai Raj · Answer

SELECT column_name(s) FROM table1 JOIN table2 ON table1.column_name = table2.column_name ;

Midhun T P · Answer

Hi You can use inner join for the same. Use below query - Select sidcreatedbyb1.name as createdbynamemodifiedbyb2.name as modifiedbyname From sub inner join base b1 on sub.createdby = b1.Id inner join base b2 on sub.modifiedby = b2.Id order by sid

Bikesh Srivastava · Answer

Simple use inner join.