SQL filter based on data in multiple rows

Question

I'm finding this a little difficult to describe, but here goes... I have a table with the following:

TITLE_CODE    PRODUCT_NUMBER    FORMAT_CODE
1234          A1                OC
1234          A2                HB
1234          A3                PB
2345          B1                OC
2345          B2                HB
3456          C1                OC

What I am looking to do is generate a query where I will only pull records that only have an "OC" format code and do not have a "HB" or "PB" format code.

Using Oracle - any help would be greatly appreciated!

I don't think what you're saying makes sense. If a record has an 'OC' code then it can't have another format code — JoeCondron
– JoeCondron, Commented May 26, 2015 at 10:48
@JoeCondron - It is making sense. The question is to only show titles that have ONLY format_code='OC', in other words title_codes that have multiple formats containing at least on of HB or PB should be ignored. — avk
– avk, Commented May 26, 2015 at 10:51
What is your expected output for the provided sample data, please? — Boneist
– Boneist, Commented May 26, 2015 at 10:56
WHERE format_code ='OC' is enough to filter the rows. What is the need to explicitly filter other codes, when all you need it format_code = 'OC'. — Lalit Kumar B
– Lalit Kumar B, Commented May 26, 2015 at 11:10
Technically, it does make sense, but the second part is redundant so I thought you wanted something different than just where format_code = 'OC' — JoeCondron
– JoeCondron, Commented May 26, 2015 at 11:15

avk · Accepted Answer · 2015-05-26 10:49:20Z

1

SELECT title_code, product_number, format_code
FROM {table} 
WHERE format_code = 'OC'
AND   title_code NOT IN ( SELECT title_code 
                          FROM  {table}
                          WHERE format_code = 'HB' 
                          OR    format_code = 'PB'
                        );

answered May 26, 2015 at 10:49

avk

8711 gold badge9 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sql-n00b Over a year ago

Thanks for the quick response - this is it exactly...!

Yuuu · Accepted Answer · 2015-05-26 10:50:50Z

0

SELECT TITLE_CODE, PRODUCT_NUMBER FROM {table} WHERE FORMAT_CODE = 'OC';

Something like this?

edited May 26, 2015 at 10:50

answered May 26, 2015 at 10:47

Yuuu

1743 silver badges12 bronze badges

3 Comments

JoeCondron Over a year ago

oracle uses as single '=' for comparison

Lalit Kumar B Over a year ago

@Alexyah +1 I upvoted, because this query seems to be what OP wants. The where condition as WHERE FORMAT_CODE = 'OC' is enough to filter out the rows. No additional condition is required on the same column.

sql-n00b Over a year ago

@Lalit Kumar B - apologies as I don't think I explained very well, but just to clarify - the query I was looking for should only return the record for title_code 3456, as this has an 'OC' format code, but does not have any other format codes. avk provided what I needed. Thanks!

Boneist · Accepted Answer · 2015-05-26 11:29:11Z

Here is an alternative, which may or may not be faster for your data than @avk's answer (you'd have to test):

with sample_data (title_code, product_number, format_code) as (select 1234, 'A1', 'OC' from dual union all
                                                               select 1234, 'A2', 'HB' from dual union all
                                                               select 1234, 'A3', 'PB' from dual union all
                                                               select 2345, 'B1', 'OC' from dual union all
                                                               select 2345, 'B2', 'HB' from dual union all
                                                               select 3456, 'C1', 'OC' from dual union all
                                                               select 7890, 'D1', 'OC' from dual union all
                                                               select 7890, 'D2', 'TD' from dual)
select title_code,
       product_number,
       format_code
from   (select title_code,
               product_number,
               format_code,
               max(case when format_code = 'OC' then 1 else 0 end) over (partition by title_code) oc,
               max(case when format_code = 'PB' then 1 else 0 end) over (partition by title_code) pb,
               max(case when format_code = 'HB' then 1 else 0 end) over (partition by title_code) hb
        from   sample_data)
where  oc = 1
and    pb = 0
and    hb = 0;

TITLE_CODE PRODUCT_NUMBER FORMAT_CODE
---------- -------------- -----------
      3456 C1             OC         
      7890 D1             OC         
      7890 D2             TD

N.B. I have added in the case where one of the title_codes has a format_code of something other than OC, HB and PB; depending on what you wanted to display, you may want to add in an additional where clause of and format_code = 'OC'. You also haven't said what you'd want displaying if there were two rows with a format_code of OC...

Collectives™ on Stack Overflow

SQL filter based on data in multiple rows

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related