Compare if Row-Combination exists in other table

  • Hi,

    I've spend the last 3 hours searching the internet and trying myself on the following problem:

    Let's assume I've the following two tables:

    create table #temp1 (name varchar(5), id int)

    insert into #temp1 (name, id)

    (

    select 'a', 5

    union

    select 'a', 8

    )

    create table #temp2 (name varchar(5), id int)

    insert into #temp2 (name, id)

    (

    select 'b', 4

    union

    select 'b', 3

    Union

    select 'c', 11

    union

    select 'c', 5

    union

    select 'd', 4

    union

    select 'e', 2

    union

    select 'e', 25

    union

    select 'e', 5

    union

    select 'e', 8

    )

    As a result I would need every name from #temp2, where both searchred id's (5 & 8) from #temp1 are included.

    In this example i would like to get 'e' as a result, because in #temp2 'e' has the id's 5, 8 and 25.

    I've tried using cross apply, but cross apply returns every Name that have one of the ids... in this case it would also return 'c'...

    Selectdistinct b.name

    from(

    Selectdistinct name

    , id

    from#temp1

    wherename = 'a'

    ) as a

    cross join(

    Selectdistinct name

    , id

    from#temp2

    ) as b

    wherea.id = b.id

  • This should do:

    SELECT name

    FROM #temp2 AS t2

    WHERE EXISTS (

    SELECT *

    FROM #temp1 AS t1

    WHERE id = t2.id

    )

    GROUP BY name

    HAVING COUNT(*) = (SELECT COUNT(*) FROM #temp1)

    -- Gianluca Sartori

  • Thanks a lot, that solution worked perfectly!!

    A colleague of mine also found a similar solution:

    SelectName1, Name2

    from(Selecta.name as Name1,

    b.name as Name2,

    count(1) as Zaehler

    from#temp1 as a

    inner join#temp2 as b on a.id = b.id

    group by a.name, b.name

    ) as c

    inner join (Selectname,

    count(1) as Zaehler

    from#temp1

    group byname

    ) as d on d.name = c.name1 and d.Zaehler = c.Zaehler

    Thank you!

  • Looks like relational division with remainder:

    High Performance Relational Division in SQL Server [/url]


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 4 posts - 1 through 3 (of 3 total)

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