August 3, 2015 at 8:34 am
Hello all,
how would I compare a list of concrete values? I have following situation and do not how how to begin therefore any hints are welcome...
---table with items
SET NOCOUNT ON;
DECLARE @items TABLE (ITEM_ID INT, ITEM_NAME VARCHAR(10))
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 10,'ITEM 1'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 11,'ITEM 2'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 12,'ITEM 3'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 13,'ITEM 4'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 14,'ITEM 5'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 15,'ITEM 6'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 16,'ITEM 7'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 17,'ITEM 8'
SELECT * FROM @items
-- table with categories
SET NOCOUNT ON;
DECLARE @categories TABLE (CAT_ID INT, CAT_NAME VARCHAR(10))
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 100,'WHITE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 101,'BLACK'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 102,'BLUE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 103,'GREEN'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 104,'YELLOW'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 105,'CIRCLE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 106,'SQUARE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 107,'TRIANGLE'
SELECT * FROM @categories
--table where categories are assigned to master categories
SET NOCOUNT ON;
DECLARE @master_categories TABLE (MASTERCAT_ID INT, CAT_ID INT)
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,100
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,101
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,102
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,103
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,104
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,105
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,106
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,107
SELECT * FROM @master_categories
-- items-categories assignment table
SET NOCOUNT ON;
DECLARE @item_categories TABLE (CAT_ID INT, ITEM_ID INT)
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,10
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,10
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,11
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,11
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 106,11
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,12
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 101,12
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,12
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,13
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,14
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,15
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 101,15
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,15
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,16
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 101,16
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 102,16
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 103,16
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,16
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 106,16
SELECT * FROM @item_categories
So now I need to query the table @t4 in and to determine the items that are assigned to category 'WHITE' in master category 1 and to 'CIRCLE' in master category 2.
The important thing is to return items that are assigned solely to 'WHITE' in master cat 1 and solely to 'CIRCLE' in master cat 2.
In the above example it would be only the ITEM 1 (id=10) that is returned:
1. ITEM 2 (id=11) is not returned because it has the assignment to category 'SQUARE' in master cat 2 additionally
2. ITEM 3 (id=12) is not returned because it has the assignment to category 'BLACK' in master cat 1 additionally
3. ITEM 4 (id=13) is not returned as it does not have assignment to category 'CIRCLE' in master cat 2 but only to 'WHITE' in master cat 1
3. ITEM 5 (id=14) is not returned as it does not have assignment to category 'WHITE' in master cat 1 but only to 'CIRCLE' in master cat 2
How could this be solved?
Many thanks in advance.
Marin
August 3, 2015 at 9:42 am
If you only need the ITEM_ID, you can use INTERSECT AND EXCEPT.
SELECT ITEM_ID
FROM @t4 t4
JOIN @t2 t2 ON t4.CAT_ID = t2.CAT_ID
WHERE t2.CAT_NAME = 'WHITE'
INTERSECT
SELECT ITEM_ID
FROM @t4 t4
JOIN @t2 t2 ON t4.CAT_ID = t2.CAT_ID
WHERE t2.CAT_NAME = 'CIRCLE'
EXCEPT
SELECT ITEM_ID
FROM @t4 t4
JOIN @t2 t2 ON t4.CAT_ID = t2.CAT_ID
WHERE t2.CAT_NAME NOT IN( 'WHITE', 'CIRCLE')
Maybe the following article (and the discussion) can give you more ideas. http://www.sqlservercentral.com/articles/T-SQL/88244/
August 3, 2015 at 2:10 pm
You can do it with a single pass thru the item_categories table -- I renamed the tables to reflect their contents. Data set up first, then the main query at the end. Edit: Split main code from data set up code.
declare @category_name1 varchar(10)
declare @category_name2 varchar(10)
set @category_name1 = 'WHITE'
set @category_name2 = 'CIRCLE'
--------------------------------------------------------------------------------
select i.ITEM_NAME
from (
select ic.ITEM_ID
from @item_categories ic
inner join @categories c on c.CAT_ID = ic.CAT_ID
cross apply (
select case when c.CAT_NAME = @category_name1 then 1
when c.CAT_NAME = @category_name2 then 2
--when c.CAT_NAME = @category_name3 then 4 ...
else -1 end as CATEGORY_MATCH
) as ca1
group by ic.ITEM_ID
having min(CATEGORY_MATCH) > 0 and sum(CATEGORY_MATCH) = 3
) as derived
inner join @items i on i.ITEM_ID = derived.ITEM_ID
--data set up with meaningful table names
set nocount on;
DECLARE @items TABLE (ITEM_ID INT, ITEM_NAME VARCHAR(10))
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 10,'ITEM 1'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 11,'ITEM 2'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 12,'ITEM 3'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 13,'ITEM 4'
INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 14,'ITEM 5'
--SELECT * FROM @items
--------
DECLARE @categories TABLE (CAT_ID INT, CAT_NAME VARCHAR(10))
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 100,'WHITE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 101,'BLACK'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 102,'BLUE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 103,'GREEN'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 104,'YELLOW'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 105,'CIRCLE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 106,'SQUARE'
INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 107,'TRIANGLE'
--SELECT * FROM @categories
--------
DECLARE @master_categories TABLE (MASTERCAT_ID INT, CAT_ID INT)
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,100
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,101
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,102
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,103
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,104
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,105
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,106
INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,107
--SELECT * FROM @master_categories
--------
DECLARE @item_categories TABLE (CAT_ID INT, ITEM_ID INT)
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,10
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,10
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,11
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,11
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 106,11
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,12
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 101,12
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,12
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,13
INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,14
--SELECT * FROM @item_categories
set nocount off
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 4, 2015 at 1:02 am
Hello Luis and Scott,
many thanks for the replies much appreciated, both suggestions are highly welcome.
However, I forgot to specify that it is not always just one category from distinct master category that can be assigned. I.e. it could be that combination e.g. 'WHITE', 'BLACK', 'CIRCLE' is sought for (would return ITEM 3 and ITEM 6, id 12 and 15)... or 'WHITE', 'CIRCLE', 'SQUARE' (would return ITEM 2, id=11) or even e.g. 'WHITE', 'BLACK', BLUE', 'GREEN', 'CIRCLE','SQUARE' (would return ITEM 6, id=17) I altered the tables for this purpose to have these cases in the definitions, also renamed tables as per suggestion from Scott, makes perfect sense...
What I ended up with is a variation of Luis' suggestion based on the discussion in provided link (thanks for that, very helpful), so for example:
if I need to search for 'WHITE', 'BLACK', 'CIRCLE' (would return ITEM 3, id=12 and ITEM 6, id=15), then I need to make the query like this:
--find all items that have assignment to 'WHITE', 'BLACK' and 'CIRCLE' and only those that have 3 distinct assignments,
--the HAVING part filters out the ones that fall within IN clauses, but do not have 3 assignments altogether
SELECT ITEM_ID FROM @item_categories
WHERE CAT_ID IN (100, 101) --master cat 1
OR CAT_ID IN (105) --master cat 2
GROUP BY ITEM_ID
HAVING COUNT (DISTINCT CAT_ID) = 3
--filter out the items that might have assignment to other categories in addition to the 3 sought ones
EXCEPT
SELECT ITEM_ID FROM @item_categories
WHERE CAT_ID NOT IN (100, 101) --master cat 1
AND CAT_ID NOT IN (105) --master cat 2
Many thanks again for your quick replies.
Marin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply