Post reply

identifying records in the same table

  • December 11, 2012 at 1:49 am

    #286075

    Hi everyone

    I have a table in SQL server that contains the following : ID, Date, Description, Type, Manager and Site. There can be more than one record per ID. The Type field contains either A, B, C or D and there should always be an A and a B record for each ID (ie ID number 00001 has 2 records in the table one of which is a type A and an another a type B). However I've noticed that in some cases the IDs are missing one of their type A and/or type B records. I would like some code that would identify when an ID has a missing A or B record in the table and tell me which ones they are.

    Hope that makes sense.

    Thanks in advance.

    BO

  • December 11, 2012 at 1:55 am

    #1567287

    select id

    from tbl

    where type in ('A','B')

    group by id

    having count(distinct type) <> 2


    Cursors never.
    DTS - only when needed and never to control.

  • December 11, 2012 at 2:11 am

    #1567294

    ByronOne (12/11/2012)

    Hi everyone

    I have a table in SQL server that contains the following : ID, Date, Description, Type, Manager and Site. There can be more than one record per ID. The Type field contains either A, B, C or D and there should always be an A and a B record for each ID (ie ID number 00001 has 2 records in the table one of which is a type A and an another a type B). However I've noticed that in some cases the IDs are missing one of their type A and/or type B records. I would like some code that would identify when an ID has a missing A or B record in the table and tell me which ones they are.

    Hope that makes sense.

    Thanks in advance.

    BO

    Can you please provide some sample data.

  • December 11, 2012 at 2:57 am

    #1567318

    Thanks Nigel - just what I was after!

    BO

  • December 11, 2012 at 3:03 am

    #1567322

    Provided sample is very basic, but should give an idea how to do what you want:

    declare @sample table (id int, [type] char(1))

    insert @sample select 1,'A'

    insert @sample select 1,'B'

    insert @sample select 2,'A'

    insert @sample select 3,'B'

    insert @sample select 4,'D'

    select s1.id, 'A' as missingType

    from @sample s1

    left join @sample sA on sA.id = s1.id and sA.[type] = 'A'

    where sA.id is null

    union

    select s1.id, 'B' as missingType

    from @sample s1

    left join @sample sB on sB.id = s1.id and sB.[type] = 'B'

    where sB.id is null

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • December 11, 2012 at 3:04 am

    #1567323

    nigelrivett (12/11/2012)

    select id

    from tbl

    where type in ('A','B')

    group by id

    having count(distinct type) <> 2

    That is good, but only will work if one of the types is missing...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • December 11, 2012 at 3:19 am

    #1567331

    Eugene

    Thanks for this, really helpful.

    BO

  • December 11, 2012 at 3:45 am

    #1567342

    Just some food for thought:

    declare @sample table (id int, [type] char(1));

    insert @sample select 1,'A' ;

    insert @sample select 1,'B';

    insert @sample select 2,'A';

    insert @sample select 3,'B';

    insert @sample select 4,'D';

    with ReqIdsTypes as (

    select distinct

    id,

    dt.[type]

    from

    @sample

    cross join (select [type] from (values ('A'),('B'))dt([type]))dt([type]))

    select id as MissingId, [type] as MissingType from ReqIdsTypes

    except

    select id, [type] from @sample;

  • December 11, 2012 at 6:45 am

    #1567416

    Thank you too Lynn!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply