CODEWITHSUNDEEP
sqloracle-database
anonymous
anonymous

Reputation: 503

Queryin One-to-Many same table in Oracle

I have a below table with sample data as below:

prod    seq catid
-------------------
prod1   0   10
prod1   1   20
prod1   2   30
prod1   3   40
prod2   0   10
prod3   0   10
prod3   1   20
prod3   2   30
prod4   0   10

I need to query above table based on catid column. Ex: If i query with catid - 10 then i need to get all products(here prod2, prod3, prod4) which has only catid as 10 all the other should be excluded. Same way if i query with catid = 10 20 30 then i need to get output as prod3, if i query with catid as 10 20 30 40 then my output should be prod1.

SELECT * FROM table WHERE catid = ALL (1,2,3,4,5)

I tried using ALL in my query but i am not able to get desired output, please help.

Upvotes: 1

Views: 348

Answers (3)

Boneist
Boneist

Reputation: 23588

Here's a way that is more generalised - it relies on you passing in a string to the query consisting of a comma separated list (no spaces) into the query:

WITH   your_table AS (SELECT 'prod1' prod, 0 seq, 10 catid FROM dual UNION ALL
                      SELECT 'prod1' prod, 0 seq, 20 catid FROM dual UNION ALL
                      SELECT 'prod1' prod, 0 seq, 30 catid FROM dual UNION ALL
                      SELECT 'prod1' prod, 0 seq, 40 catid FROM dual UNION ALL
                      SELECT 'prod2' prod, 0 seq, 10 catid FROM dual UNION ALL
                      SELECT 'prod3' prod, 0 seq, 10 catid FROM dual UNION ALL
                      SELECT 'prod3' prod, 0 seq, 20 catid FROM dual UNION ALL
                      SELECT 'prod3' prod, 0 seq, 30 catid FROM dual UNION ALL
                      SELECT 'prod4' prod, 0 seq, 10 catid FROM dual)
-- end of mimicking your table with sample data in it
-- see sql below:
SELECT   yt.prod
FROM     your_table yt
GROUP BY yt.prod
HAVING   min(CASE WHEN ','||:p_string_to_compare||',' LIKE '%,'||yt.catid||',%' THEN 'Y'
                  ELSE 'N'
             END) = 'Y'
AND      count(*) = regexp_count(:p_string_to_compare, ',') + 1;

With :p_string = '10,40,30,20':

PROD
-----
prod1

With :p_string = '10,20,30':

PROD
-----
prod3

With :p_string = '10, 20':

no rows returned

With :p_string = '10':

PROD
-----
prod4
prod2

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

You can get the expected prod value using the query:

SELECT prod
FROM mytable
GROUP BY prod
HAVING COUNT(DISTINCT catId) = 5 AND 
       COUNT(CASE WHEN catId NOT IN (1,2,3,4,5) THEN 1 END) = 0

The above query returns products having 5 distinct catId values. None of these values doesn't belong to (1,2,3,4,5) set of values.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522161

One way to do this is using conditional aggregation:

SELECT prod
FROM yourTable
GROUP BY prod
HAVING SUM(CASE WHEN catid = 1 THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN catid = 2 THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN catid = 3 THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN catid = 4 THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN catid = 5 THEN 1 ELSE 0 END) > 0

Upvotes: 0

Related Questions