none
select rows from an unNormalized table based on data from a Normalized table

    Question

  • Is there a join type I could use or something else to select rows from an unNormalized table based on the data from a Normalized table? Here are the 2 tables.  I want to select the rows from #tmp1 where in_out column values in #tmp2 are the same for each of the columns in each row of #tmp1

    create

    table #tmp1(rowID int identity(1,1), cur varchar(10), prev varchar(10))

    insert into #tmp1 select 'sam', 'tam' union all select 'pam', 'zam' union all select 'kam', 'ram' union all select 'mam', 'bam' create table #tmp2(title varchar(10), in_out varchar(10)) insert into #tmp2 select 'sam', 'in' union all select 'tam', 'in' union all select 'pam', 'in' union all select 'zam', 'out' union all select 'kam', 'out' union all select 'ram', 'out' union all select 'mam', 'out' union all select 'bam', 'in'

    my result set from #tmp1 then would be these rows:

    #tmp1

    cur     prev       in_out
    sam     tam        in       --both columns in #tmp1 have same in_out in #tmp2
    kam     ram        out

    --what is the Tsql to accomplish this?

    Thanks


    Rich P



    • Edited by Rich P123 Wednesday, December 18, 2013 6:24 PM ....
    Wednesday, December 18, 2013 6:22 PM

Answers

  • Here is one option

    with cte as (select x.title as title1, y.title as title2, x.in_out from #tmp2 as x inner join #tmp2 as y on x.title <> y.title and x.in_out = y.in_out ) 
    select main.*, cte.in_out from #tmp1 as main inner join cte on main.cur = cte.title1 and main.prev = cte.title2
    order by main.rowID

    • Marked as answer by Rich P123 Wednesday, December 18, 2013 7:14 PM
    Wednesday, December 18, 2013 6:34 PM

All replies

  • Here is one option

    with cte as (select x.title as title1, y.title as title2, x.in_out from #tmp2 as x inner join #tmp2 as y on x.title <> y.title and x.in_out = y.in_out ) 
    select main.*, cte.in_out from #tmp1 as main inner join cte on main.cur = cte.title1 and main.prev = cte.title2
    order by main.rowID

    • Marked as answer by Rich P123 Wednesday, December 18, 2013 7:14 PM
    Wednesday, December 18, 2013 6:34 PM
  • ;WITH cte  
    AS 
    (
    SELECT  rowID ,
            cur ,
            prev ,
            title ,
            in_out	
    	,COUNT(*) 
    	  OVER	
    	   (PARTITION BY rowID
    		,cur , prev , in_out
    		) AS Cnt
    	,ROW_NUMBER()
    	  OVER	
    	  (PARTITION BY rowID
    	    ,cur, prev, in_out
    		ORDER BY rowID
    		) AS Rn
    FROM #tmp1 AS a
    LEFT JOIN #tmp2 AS b
     ON a.prev = b.title
          OR a.cur = b.title
    )
    SELECT cur
     ,prev
     ,in_out
    FROM cte
    WHERE Rn = 1 
     AND Cnt = 2


    Narsimha

    Wednesday, December 18, 2013 6:46 PM