none
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

    Thursday, January 03, 2013 1:46 PM

Answers

  • 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

    Monday, January 07, 2013 9:46 AM

All replies

  • 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

    Friday, January 04, 2013 6:27 PM
  • 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


    Monday, January 07, 2013 9:23 AM
  • 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

    Monday, January 07, 2013 9:46 AM