Select header records that contain records from second table 2

[MarkSweetland]

I have been looking at this all day and still can't get my head in the right place.

I have a table with header and res_ids that I need to extract only the header records that match ALL the records in a filter table.

For example, my header record:

header_id res_id
30320 117
30324 915
30325 117
30325 915
30328 117
30541 915
30542 915
30840 915
30864 915

and my filter table:

res_id
117
915

How can I return only those header_ids that have ALL the res_ids. In the example, I would only return header_id 30325 since a record with that header_id exists with matching res_ids in the filter table.






Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[Qik3Coder]

That was fun...

Declare @header as table(header_id int, res_id int)
INSERT INTO @header
SElECT 30320,	117 UNION
SElECT 30324,	915 UNION
SElECT 30325,	117 UNION
SElECT 30325,	915 UNION
SElECT 30328,	117 UNION
SElECT 30541,	915 UNION
SElECT 30542,	915 UNION
SElECT 30840,	915 UNION
SElECT 30864,	915 

Declare @res as table(res_id int)
INSERT INTO @res
Select 117 UNION
SELECT 915

--Select * from @header
--Select * from @res

Select h.header_id, r.res_id, MIN(CASE WHEN h.res_id = r.res_id THEN 0 ELSE 1 END) Missing
from @header h
left outer join @res r ON
1=1
GROUP BY h.header_id, r.res_id
ORDER BY h.header_id, r.res_id

Select a.Header_id, Sum(a.missing)
FROM (
	Select h.header_id, r.res_id, MIN(CASE WHEN h.res_id = r.res_id THEN 0 ELSE 1 END) Missing
	from @header h
	left outer join @res r ON
	1=1
	GROUP BY h.header_id, r.res_id
	) as a
Group by a.Header_Id
Having SUM(Missing) = 0
Order by a.Header_id

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[simian336]

Can there also multiples of each like?

header_id res_id
30320 117
30320 117
30324 915
30324 915
30325 117
30325 915

or is there a single 915 or 117?

Simi

 

[gmmastros]

Here's another way...

Declare @Header Table(header_id Int, res_id Int)
Insert Into @Header Values(30320,117)
Insert Into @Header Values(30324,915)
Insert Into @Header Values(30325,117)
Insert Into @Header Values(30325,915)
Insert Into @Header Values(30328,117)
Insert Into @Header Values(30541,915)
Insert Into @Header Values(30542,915)
Insert Into @Header Values(30840,915)
Insert Into @Header Values(30864,915)

Declare @Filter Table(res_id Int)
Insert Into @Filter Values(117)
Insert Into @Filter Values(915)

Select H.Header_Id
From   @Header H
       Inner Join @Filter F
         On H.res_id = F.res_id
Group By H.header_id
Having Count(Distinct H.res_id) = (Select Count(*) From @Filter)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Qik3Coder,

I was looking at the query you suggested. I noticed the Left Join on 1=1 and thought it was a little odd. Then I realized you actually meant to use a cross join. Written as a cross join, the results are the same, but I think the intent of the query is a bit easier to understand.

Select h.header_id, r.res_id, MIN(CASE WHEN h.res_id = r.res_id THEN 0 ELSE 1 END) Missing
from @header h
Cross join @res r
GROUP BY h.header_id, r.res_id
ORDER BY h.header_id, r.res_id

Note that Left Outer was replaced with Cross and the ON clause was removed. Both queries are functionally identical and performance is not impacted. I would still encourage you to use a cross join in situations like this because (in my opinion) it makes the code a little easier to understand.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PWise]

how about

Select header_id, res_id
from mytable
inner join (select 117 as res_id)res117
on res117.res_id=mytable.res_id
inner join (select 915 as res_id)res915
on res915.res_id=mytable.res_id

 

[Qik3Coder]

@Gmmastros, Thank you! That's what i was looking for, and couldn't remember the keyword.

@PWise, The problem with that is that it similar to CROSS TAB reports, you need to add a join for each parameter.

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[MarkSweetland]

Awesome. This will get me in the right track.

Thanks guys...


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[markros]

Hi guys,

I know that the query that George wrote is often used for this type of the queries, but this is not the best performant query. Peter Larsson looked into this problem in depth. Here is one of his articles on this exact topic (the name of these kind of problems is 'Relational Division')

http://sqlblog.com/blogs/peter_larsson/archive/2010/06/30/relational-algebra.aspx

BTW, your case is even a bit simpler than the one considered in that blog.

PluralSight Learning Library

 

[SwePeso]

DECLARE @Header TABLE
(
HeaderID INT NOT NULL,
ResID INT NOT NULL
)

INSERT @Header
SELECT 30320, 117 UNION ALL
SELECT 30324, 915 UNION ALL
SELECT 30325, 117 UNION ALL
SELECT 30325, 915 UNION ALL
SELECT 30328, 117 UNION ALL
SELECT 30541, 117 UNION ALL
SELECT 30541, 915 UNION ALL
SELECT 30541, 951 UNION ALL
SELECT 30864, 915

DECLARE @Res TABLE
(
ResID INT NOT NULL
)

INSERT @Res
SELECT 117 UNION ALL
SELECT 915

-- Exact division
SELECT hc.HeaderID
FROM (
SELECT HeaderID,
COUNT(*) AS ResItems
FROM @Header
GROUP BY HeaderID
) AS hc
INNER JOIN (
SELECT COUNT(*) AS ResItems
FROM @Res
) AS rc ON rc.ResItems = hc.ResItems
INNER JOIN @Header AS h ON h.HeaderID = hc.HeaderID
INNER JOIN @Res AS r ON r.ResID = h.ResID
GROUP BY hc.HeaderID
HAVING COUNT(*) = MIN(hc.ResItems)

-- With remainder
SELECT hc.HeaderID
FROM (
SELECT HeaderID,
COUNT(*) AS ResItems
FROM @Header
GROUP BY HeaderID
) AS hc
INNER JOIN (
SELECT COUNT(*) AS ResItems
FROM @Res
) AS rc ON rc.ResItems <= hc.ResItems
INNER JOIN @Header AS h ON h.HeaderID = hc.HeaderID
INNER JOIN @Res AS r ON r.ResID = h.ResID
GROUP BY hc.HeaderID
HAVING COUNT(*) = MIN(rc.ResItems)