Forum Stats

  • 3,759,229 Users
  • 2,251,515 Discussions
  • 7,870,546 Comments

Discussions

sq lquery to find specific values

ora_1978
ora_1978 Member Posts: 486 Bronze Badge

create table search_calc (calc_id number , product varchar2(10));

insert into search_calc values (1, 'BA');

insert into search_calc values (1, 'BL');

insert into search_calc values (1, 'XDL');

insert into search_calc values (1, 'ST');

insert into search_calc values (2, 'BA');

insert into search_calc values (2, 'BL');

insert into search_calc values (3, 'BA');

insert into search_calc values (3, 'BL');

insert into search_calc values (3, 'HA');

insert into search_calc values (2, 'BL');

insert into search_calc values (2, 'BA');

insert into search_calc values (2, 'BL');

commit;

I need a query to get calc id 2 which has BA,BL but not 1 AND 3 which also have BA, BL

Note: I should NOT use the below query :

SELECT * FROM SEARCH_CALC WHERE CALC_ID = 2 ; -------I do not want filter calc_id = 2;

Thanks ,

Vinodh

Tagged:

Best Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond
    edited Sep 7, 2021 1:14PM Accepted Answer


    @User_H3J7U if you're going to provide an answer, at least try and be helpful to the OP. Just single word answers or terse sentances (as you've been doing) is no help to people. Sure, as an expert I know that "having" is a key part of the required solution, but that's only because I already know. You have to assume the OP doesn't know, and your answer is not going to help them to understand. If you can't provide helpful answers and explanations then please don't bother posting, as it's no better than trolling.

    @ora_1978 Here's one solution...

    SQL> with search_calc (calc_id, product) as (
      2    select 1, 'BA' from dual union all
      3    select 1, 'BL' from dual union all
      4    select 1, 'XDL' from dual union all
      5    select 1, 'ST' from dual union all
      6    select 2, 'BA' from dual union all
      7    select 2, 'BL' from dual union all
      8    select 3, 'BA' from dual union all
      9    select 3, 'BL' from dual union all
     10    select 3, 'HA' from dual union all
     11    select 2, 'BL' from dual union all
     12    select 2, 'BA' from dual union all
     13    select 2, 'BL' from dual
     14    -- additional case where calc_id only has 1 of the required products
     15    union all select 4, 'BA' from dual
     16    )
     17  -- end of test data
     18  select calc_id
     19  from   search_calc
     20  group by calc_id
     21  having count(distinct product) = 2
     22     and max(product) in ('BA','BL')
     23     and min(product) in ('BA','BL') -- correction added
     24  /
    
    
       CALC_ID
    ----------
             2
    

    This simply groups the calc_id rows together, counting how many distinct products there are for each, and we know we want to have exactly 2 distinct products. We also want those products to be BA and BL. The HAVING clause can be used to apply "where"-like conditions to groupings that use the aggregate functions. As such, we need to use an aggregate to check the minimum and maximum codes meet the two required codes..

    So this is like saying, I need to have 2 distinct products in the group and they should be BA and BL.

    Edit: Correction to having clause as discussed below

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,063 Red Diamond
    Accepted Answer

    Hi, @ora_1978

    Thanks for posting the CREATE TABLE and INSERT statements; that's very helpful! Don't forget to post the exact results you want from that same data. It's unclear if you want one row of output, like this:

       CALC_ID
    ----------
             2
    

    or five rows, like this:

       CALC_ID PRODUCT
    ---------- ----------
             2 BA
             2 BA
             2 BL
             2 BL
             2 BL
    

    To get one row of output, you can use aggregate functions, GROUP BY and HAVING. If you want five rows, you can use analytic functions, PARTITION BY and WHERE, like this:

    WITH  got_cnts  AS
    (
      SELECT  calc_id, product
      ,	  COUNT (DISTINCT product) OVER (PARTITION BY calc_id) AS total_products
      ,	  COUNT ( CASE
    		    WHEN product IN ('BA', 'BL')
    		    THEN NULL
    		    ELSE 'BAD'
    		  END
      	  	 )	      OVER (PARTITION BY calc_id)	 AS bad_products
      FROM  search_calc
    )
    SELECT  calc_id, product
    FROM	got_cnts
    WHERE	total_products = 2
    AND	bad_products  = 0
    ;
    

    The query above displays every row from the table for each calc_id that has both products 'BA' and 'BL', but no rows where product has any other value (or where product is NULL).

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 485 Bronze Trophy
  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond
    edited Sep 7, 2021 1:14PM Accepted Answer


    @User_H3J7U if you're going to provide an answer, at least try and be helpful to the OP. Just single word answers or terse sentances (as you've been doing) is no help to people. Sure, as an expert I know that "having" is a key part of the required solution, but that's only because I already know. You have to assume the OP doesn't know, and your answer is not going to help them to understand. If you can't provide helpful answers and explanations then please don't bother posting, as it's no better than trolling.

    @ora_1978 Here's one solution...

    SQL> with search_calc (calc_id, product) as (
      2    select 1, 'BA' from dual union all
      3    select 1, 'BL' from dual union all
      4    select 1, 'XDL' from dual union all
      5    select 1, 'ST' from dual union all
      6    select 2, 'BA' from dual union all
      7    select 2, 'BL' from dual union all
      8    select 3, 'BA' from dual union all
      9    select 3, 'BL' from dual union all
     10    select 3, 'HA' from dual union all
     11    select 2, 'BL' from dual union all
     12    select 2, 'BA' from dual union all
     13    select 2, 'BL' from dual
     14    -- additional case where calc_id only has 1 of the required products
     15    union all select 4, 'BA' from dual
     16    )
     17  -- end of test data
     18  select calc_id
     19  from   search_calc
     20  group by calc_id
     21  having count(distinct product) = 2
     22     and max(product) in ('BA','BL')
     23     and min(product) in ('BA','BL') -- correction added
     24  /
    
    
       CALC_ID
    ----------
             2
    

    This simply groups the calc_id rows together, counting how many distinct products there are for each, and we know we want to have exactly 2 distinct products. We also want those products to be BA and BL. The HAVING clause can be used to apply "where"-like conditions to groupings that use the aggregate functions. As such, we need to use an aggregate to check the minimum and maximum codes meet the two required codes..

    So this is like saying, I need to have 2 distinct products in the group and they should be BA and BL.

    Edit: Correction to having clause as discussed below

  • User_H3J7U
    User_H3J7U Member Posts: 485 Bronze Trophy

    @BluShadow If you can't provide helpful answers and explanations then please don't bother posting, as it's no better than trolling.

    Many words are not helpful for the author... and wrong solution.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond

    You're more than welcome to provide a working solution yourself. Something more than just a single word would, I'm sure, be helpful for the OP.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,063 Red Diamond
    Accepted Answer

    Hi, @ora_1978

    Thanks for posting the CREATE TABLE and INSERT statements; that's very helpful! Don't forget to post the exact results you want from that same data. It's unclear if you want one row of output, like this:

       CALC_ID
    ----------
             2
    

    or five rows, like this:

       CALC_ID PRODUCT
    ---------- ----------
             2 BA
             2 BA
             2 BL
             2 BL
             2 BL
    

    To get one row of output, you can use aggregate functions, GROUP BY and HAVING. If you want five rows, you can use analytic functions, PARTITION BY and WHERE, like this:

    WITH  got_cnts  AS
    (
      SELECT  calc_id, product
      ,	  COUNT (DISTINCT product) OVER (PARTITION BY calc_id) AS total_products
      ,	  COUNT ( CASE
    		    WHEN product IN ('BA', 'BL')
    		    THEN NULL
    		    ELSE 'BAD'
    		  END
      	  	 )	      OVER (PARTITION BY calc_id)	 AS bad_products
      FROM  search_calc
    )
    SELECT  calc_id, product
    FROM	got_cnts
    WHERE	total_products = 2
    AND	bad_products  = 0
    ;
    

    The query above displays every row from the table for each calc_id that has both products 'BA' and 'BL', but no rows where product has any other value (or where product is NULL).

  • mathguy
    mathguy Member Posts: 10,081 Blue Diamond

    @BluShadow

    I agree that one-word answers aren't particularly helpful.

    Wrong answers aren't either. User <whatever> has posted wrong answers on occasion, too; but in this thread, he is correct about your answer being wrong. Did you check that, after he made this claim, or did you dismiss it, considering who it's coming from?

    The OP already accepted your answer (which probably means he thinks it's correct). That makes the answer even less helpful.

    Your solution only guarantees that the selected id's have exactly two distinct products, and the MAX (in lexicographic order) is either 'BA' or 'BL'. It does not say anything about the MIN - your query will select id = 8 if it has products 'AF' and 'BA' for example. Not what the OP needs.

    To make sure the answer will be correct for future readers, you may want to edit it to add a condition on MIN as well, and to change the plain-English explanation accordingly.

  • mathguy
    mathguy Member Posts: 10,081 Blue Diamond

    @ora_1978

    Can product be null? If it can't, you should say so when you ask the question (and perhaps you should also have a not null constraint in the table definition). If it can be null, how should that be handled?

    Note that the two answers you "accepted" handle null differently - so they can't both be correct (even after BluShadow fixes his answer), unless product can never be null,

  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond

    @mathguy

    Thanks for pointing out the mistake, that was just silly on my part (we all make mistakes!). I've now edited and corrected it.

    At least you had the graciousness to point out what the mistake was rather than just saying it didn't work (which again just highlights the problem of short answers with no explanation) and leaving me to wonder what the issue was.

    👍️