Comparing Results

Question

Comparing Results

Grinja

Hall of Fame

Points: 3590
More actions
July 29, 2008 at 6:53 am

#194963

A simpliefied sample below indicates table CoreItem and table SubItem.
I need to return the CoreItem where the CoreItem category is not part of at least one SubItem category. Items (both Core and sub) can belong to 1 > categories.
For the sample below the the result should return CoreItemID 2 because niether category A, C or D belongs to SubItem 4 or 5
CoreItem
CoreItem ID Category
1 A
1 B
1 C
2 A
2 C
2 D
SubItem
SubItem ID CoreItemID Category
1 1 A
2 1 B
3 1 D
4 2 B
5 2 B
*Edited test data*
Any help would be welcome.

Viewing 7 posts - 1 through 6 (of 6 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hi Grinja

I suspect there may be a flaw in the test data. Try running this and examine the results. I'm guessing that you want to eliminate category 'D' for coreitemid=2 in the subitems table. Please note that this code is not intended to solve your problem, but to arrive at a more accurate question!

CREATE TABLE #CoreItem (CoreItemID INT, Category CHAR(1))

INSERT INTO #CoreItem (CoreItemID, Category)

SELECT 1, 'A' UNION ALL

SELECT 1, 'B' UNION ALL

SELECT 1, 'C' UNION ALL

SELECT 2, 'A' UNION ALL

SELECT 2, 'C' UNION ALL

SELECT 2, 'D'

CREATE TABLE #SubItem (SubItemID INT, CoreItemID INT, Category CHAR(1))

INSERT INTO #SubItem (SubItemID, CoreItemID, Category)

SELECT 1, 1, 'A' UNION ALL

SELECT 2, 1, 'B' UNION ALL

SELECT 3, 2, 'D' UNION ALL

SELECT 4, 2, 'B' UNION ALL

SELECT 5, 2, 'B'

SELECT a.*, b.*

FROM #CoreItem a

LEFT JOIN #SubItem b ON b.CoreItemID = a.CoreItemID AND b.Category = a.Category

--WHERE b.CoreItemID IS NULL

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grinja Hall of Fame Points: 3590 More actions · Answer 2

Hi my apoligies for the test data ... i've modified it a bit.

I'm trying to identify which CoreItem has SubItems with categories which do not correspond to any of the CoreItem categories.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

No problem Grinja.

This would appear to do the trick:

DROP TABLE #CoreItem

DROP TABLE #SubItem

CREATE TABLE #CoreItem (CoreItemID INT, Category CHAR(1))

INSERT INTO #CoreItem (CoreItemID, Category)

SELECT 1, 'A' UNION ALL

SELECT 1, 'B' UNION ALL

SELECT 1, 'C' UNION ALL

SELECT 2, 'A' UNION ALL

SELECT 2, 'C' UNION ALL

SELECT 2, 'D' UNION ALL

SELECT 3, 'A' UNION ALL

SELECT 3, 'B' UNION ALL

SELECT 3, 'C'

CREATE TABLE #SubItem (SubItemID INT, CoreItemID INT, Category CHAR(1))

INSERT INTO #SubItem (SubItemID, CoreItemID, Category)

SELECT 1, 1, 'A' UNION ALL

SELECT 2, 1, 'B' UNION ALL

SELECT 3, 1, 'D' UNION ALL -- no match

SELECT 4, 2, 'B' UNION ALL -- no match

SELECT 5, 2, 'B' UNION ALL -- no match

SELECT 6, 3, 'A' UNION ALL

SELECT 7, 3, 'B' UNION ALL

SELECT 8, 3, 'C'

-- gives SubItems with categories which do not correspond to any of the CoreItem categories

SELECT a.*

FROM #SubItem a

LEFT JOIN #CoreItem b ON b.CoreItemID = a.CoreItemID AND b.Category = a.Category

WHERE b.CoreItemID IS NULL

-- identifies which CoreItem has SubItems with categories which do not correspond to any of the CoreItem categories

SELECT DISTINCT(c.CoreItemID)

FROM #CoreItem c

INNER JOIN (SELECT DISTINCT(a.CoreItemID)

FROM #SubItem a

LEFT JOIN #CoreItem b ON b.CoreItemID = a.CoreItemID AND b.Category = a.Category

WHERE b.CoreItemID IS NULL

) d ON d.CoreItemID = c.CoreItemID

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grinja Hall of Fame Points: 3590 More actions · Answer 4

Thank you Chris.

The problem I have is this though:

CoreItem 1 should not be returned because at least one of it's SubItem (1 and 2) have matching categories. Even if CoreItem3 does not match any categories.

Only CoreItem 2 should be returned in the Query.

A CoreItem should have at least one SubItem with a matching category.

I'm trying to identify CoreItem where this is not the case.

I apolagise if the wording is unclear...

Thanks again for spending time to help.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

That's ok Grinja, my fault, not enough coffee. This should do the trick:

-- identifies which CoreItem has SubItems with categories which do not correspond to any of the CoreItem categories

SELECT DISTINCT(c.CoreItemID)

FROM #CoreItem c

LEFT JOIN (SELECT a.CoreItemID

FROM #CoreItem a

INNER JOIN #SubItem b ON b.CoreItemID = a.CoreItemID AND b.Category = a.Category

) d ON d.CoreItemID = c.CoreItemID

WHERE d.CoreItemID IS NULL

The inner SELECT picks up matches between both tables. The outer select picks up rows from CoreItem which don't have a matching row in the inner select, matching on CoreItemID.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grinja Hall of Fame Points: 3590 More actions · Answer 6

Thanks Chris ! This was is what I was looking for.