locked
One to one Relationship between columns RRS feed

  • Question

  • HI All

    i have table called Temp1 (Lvl2,Lvl1)

    the data is as fallows 

    Temp1

    Lvl2 lvl1

    1 a

    2 b

    3 s

    4 a

    5 g

    what i want to check is Lvl2 should have only one to one relationship with lvl1 

    i want to know which rows dont have one - one realtionship 

    for the above 1,4 dont have one -one relationship 

    i want the query which will give the output as 1,4 

     

    Kind Regards,

    Bhasker 

     

     


    • Edited by Bhasker Alle Friday, April 15, 2011 5:14 AM font
    Friday, April 15, 2011 5:14 AM

Answers

  • Declare @MyTable Table(lvl2 int, lvl1 varchar(10))
    
    Insert into @MyTable
    Select 1,'a' Union All 
    Select 2,'b' Union All 
    Select 3,'s' Union All 
    Select 4,'a' Union All 
    Select 5,'g' Union All 
    Select 6,'h' Union All 
    Select 7,'s' Union All 
    Select 8,'i' Union All 
    Select 9,'j' 
    
    
    Select	A.lvl2, B.lvl1
    From	@mytable A
    			inner join
    		(
    			Select lvl1
    			From @myTable
    			Group by lvl1
    			Having count(*) > 1
    		) B
    			On A.lvl1 = B.lvl1
    Order by	B.lvl1, A.lvl2
    
    • Marked as answer by KJian_ Thursday, April 21, 2011 6:13 AM
    Friday, April 15, 2011 5:39 AM

All replies

  • Select t1.Lvl2, t2.Lvl2
    From Temp1 t1
    Inner Join Temp1 t2 On t1.lvl1 = t2.lbl1 And t1.Lvl2 < t2.Lvl2;
    
    Tom
    Friday, April 15, 2011 5:22 AM
  • Declare @MyTable Table(lvl2 int, lvl1 varchar(10))
    
    Insert into @MyTable
    Select 1,'a' Union All 
    Select 2,'b' Union All 
    Select 3,'s' Union All 
    Select 4,'a' Union All 
    Select 5,'g' Union All 
    Select 6,'h' Union All 
    Select 7,'s' Union All 
    Select 8,'i' Union All 
    Select 9,'j' 
    
    
    Select	A.lvl2, B.lvl1
    From	@mytable A
    			inner join
    		(
    			Select lvl1
    			From @myTable
    			Group by lvl1
    			Having count(*) > 1
    		) B
    			On A.lvl1 = B.lvl1
    Order by	B.lvl1, A.lvl2
    
    • Marked as answer by KJian_ Thursday, April 21, 2011 6:13 AM
    Friday, April 15, 2011 5:39 AM
  • Try below code

    CREATE TABLE #Temp1

    (Lvl2 int null, Lvl1 char(1)null)

     insert into #Temp1

    select 1,

    'a'

     union all

    select 2,

    'b' 

    union all

    select 3, 

    's'

     union all

    select 4, 

    'a'

     union all

    select 5, 

    'g'

     select Lvl2 from #Temp1

    where lvl1 in

    ( Select Lvl1 from #Temp1 group by Lvl1

    having count(lvl1)>1

    )

     drop table #Temp1

     


    Friday, April 15, 2011 5:42 AM