Home Forums SQL Server 2005 T-SQL (SS2K5) find out which record_ids match on 2 fields and not match a third field. RE: find out which record_ids match on 2 fields and not match a third field.

  • Try something like this:

    select distinct tempcheck_id, record_id

    from #tempcheck

    inner join

    (select tempcheck_id, min(record_id) as min_id

    from #tempcheck

    group by tempcheck_id) Sub

    on main.tempcheck_id = sub.tempcheck_id

    and main.record_id = sub.min_id

    except

    select t1.tempcheck_id, t1.record_id

    from #tempcheck t1

    inner join #tempcheck t2

    on t1.record_id = t2.record_id

    and t1.tempcheck_id < t2.tempcheck_id

    and t1.item = t2.item

    and t1.i_desc = t2.i_desc

    and t1.s_desc = t2.s_desc

    and t1.m_desc = t2.m_desc

    inner join #tempcheck t3

    on t1.record_id = t3.record_id

    and t2.tempcheck_id < t3.tempcheck_id

    and t1.item = t3.item

    and t1.i_desc = t3.i_desc

    and t1.s_desc = t3.s_desc

    and t1.m_desc = t3.m_desc

    You may need to play around with the join criteria to get exactly what you want, but it should put you in the right direction.

    I noticed you have four of some record IDs. The above code will find if any three of them match, ignoring and unmatched fourth record. If you want to include unmatched fourth records, create a second query that extends the joins by one.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon