none
Keep Variable where another has multiple records RRS feed

  • Question

  • Hi, 

    I have a variable called 'New_ID'.

    I need to bring back this variable where another variable 'UID' has more than one record linked to 'New_ID'

    Example below shows what I would expect to see as an output:

    New_ID     UID  

    124            10  

    124            12

    If New_ID only have 1 value for UID, I don't need to see it. 

    I hope this makes sense.

    Thanks

    Wednesday, August 14, 2019 1:17 PM

Answers

  • Check an example:

    declare @mytable as table (New_ID int, UID int)
    
    insert into @mytable values
    	( 124, 10 ),
    	( 124, 12 ),
    	( 125, 17 ),
    	( 126, 19 ),
    	( 126, 20 ),
    	( 127, 21 )
    
    select *
    from @mytable
    where New_ID not in (
    	select New_ID
    	from @mytable
    	group by New_ID
    	having count(*)=1
    )
    

    Thursday, August 15, 2019 5:57 AM

All replies

  • So if the New_Id column has 124 and 125 what value this variable should have ?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, August 14, 2019 1:24 PM
    Moderator
  • If New_ID 125 has more than 1 record in 'UID', then I need that to be returned within the output. If it only has the one 'UID' record then it can be ignored.

    Wednesday, August 14, 2019 1:35 PM
  • Check an example:

    declare @mytable as table (New_ID int, UID int)
    
    insert into @mytable values
    	( 124, 10 ),
    	( 124, 12 ),
    	( 125, 17 ),
    	( 126, 19 ),
    	( 126, 20 ),
    	( 127, 21 )
    
    select *
    from @mytable
    where New_ID not in (
    	select New_ID
    	from @mytable
    	group by New_ID
    	having count(*)=1
    )
    

    Thursday, August 15, 2019 5:57 AM
  • Hi Bone,

    If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.

    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    Best regards,
    Cathy Ji


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, August 16, 2019 9:33 AM