March 10, 2015 at 9:10 am
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
March 10, 2015 at 10:41 am
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
March 18, 2015 at 2:33 am
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!
March 22, 2015 at 8:54 pm
Looks like relational division with remainder:
High Performance Relational Division in SQL Server [/url]
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