Get single row joining two tables

Question

Declare

@Table1 table(proc_name varchar(200),RoleName varchar(200))

Declare

@Table2 table(proc_name varchar(200),Value int)

Insert

Into @Table1 Values('Proc1','Role1')

Insert

into @Table1 Values('Proc1','Role2')

Insert

Into @Table1 Values('Proc2','Role3')

Insert

into @Table1 Values('Proc2','Role4')

Insert

into @Table2 Values('Proc1',10)

Insert

into @Table2 Values('Proc1',11)

Insert

into @Table2 Values('Proc2',20)

Insert

into @Table2 Values('Proc2',21)

--Want output like joing both tables

ProcName   Value    RoleName

(picked top row from Table1)

Proc1         10        Role1

Proc1         11       Role1

Proc2         20       Role3

Proc2         21       Role3

Answer 1

This is your solution:

Declare @Table1 table(proc_name varchar(200),RoleName varchar(200))
Declare @Table2 table(proc_name varchar(200),Value int)
Insert Into @Table1 Values('Proc1','Role1')
Insert into @Table1 Values('Proc1','Role2')
Insert Into @Table1 Values('Proc2','Role3')
Insert into @Table1 Values('Proc2','Role4')
Insert into @Table2 Values('Proc1',10)
Insert into @Table2 Values('Proc1',11)
Insert into @Table2 Values('Proc2',20)
Insert into @Table2 Values('Proc2',21)

--Select
select distinct t1.proc_name, t2.Value, (select top 1 RoleName from @Table1 t where t.proc_name=t1.proc_name) as RoleName
from @Table2 t2
inner join (select proc_name, RoleName from @Table1 group by proc_name, RoleName) t1 on t1.proc_name=t2.proc_name

Answer 2

Thanks irusul for reply.I think it has to be t2(underlined) instead of t1

select

distinct t2.proc_name, t2.Value, (select top 1 RoleName from @Table1 t where t.proc_name=t2.proc_name) as RoleName

from

@Table2 t2

Answer 3

This is your solution:

Declare @Table1 table(proc_name varchar(200),RoleName varchar(200))
Declare @Table2 table(proc_name varchar(200),Value int)
Insert Into @Table1 Values('Proc1','Role1')
Insert into @Table1 Values('Proc1','Role2')
Insert Into @Table1 Values('Proc2','Role3')
Insert into @Table1 Values('Proc2','Role4')
Insert into @Table2 Values('Proc1',10)
Insert into @Table2 Values('Proc1',11)
Insert into @Table2 Values('Proc2',20)
Insert into @Table2 Values('Proc2',21)

--Select
select distinct t1.proc_name, t2.Value, (select top 1 RoleName from @Table1 t where t.proc_name=t1.proc_name) as RoleName
from @Table2 t2
inner join (select proc_name, RoleName from @Table1 group by proc_name, RoleName) t1 on t1.proc_name=t2.proc_name

TOP is never guaranteed to return the first one unless you explicitly force a ORDER BY even though it might in this scenario. A explicit order by is required. I would suggest.

Declare @Table1 table(proc_name varchar(200),RoleName varchar(200))
Declare @Table2 table(proc_name varchar(200),Value int)
Insert Into @Table1 Values('Proc1','Role1')
Insert into @Table1 Values('Proc1','Role2')
Insert Into @Table1 Values('Proc2','Role3')
Insert into @Table1 Values('Proc2','Role4')
Insert into @Table2 Values('Proc1',10)
Insert into @Table2 Values('Proc1',11)
Insert into @Table2 Values('Proc2',20)
Insert into @Table2 Values('Proc2',21)

SELECT	T2.proc_name, T2.value, T.RoleName
  FROM	@Table2 T2
  JOIN	(
			SELECT	Proc_Name, MIN(RoleName) AS RoleName
			  FROM	@Table1 T1
	      GROUP BY	proc_name
		) T
	ON	T2.proc_name = T.proc_name