Discussions
Categories
 381.9K All Categories
 2.1K Data
 207 Big Data Appliance
 1.9K Data Science
 447.5K Databases
 220.8K General Database Discussions
 3.7K Java and JavaScript in the Database
 24 Multilingual Engine
 516 MySQL Community Space
 464 NoSQL Database
 7.8K Oracle Database Express Edition (XE)
 2.9K ORDS, SODA & JSON in the Database
 474 SQLcl
 3.9K SQL Developer Data Modeler
 186K SQL & PL/SQL
 20.9K SQL Developer
 292.7K Development
 7 Developer Projects
 125 Programming Languages
 289.5K Development Tools
 95 DevOps
 3K QA/Testing
 645.5K Java
 23 Java Learning Subscription
 36.9K Database Connectivity
 150 Java Community Process
 104 Java 25
 22.1K Java APIs
 137.8K Java Development Tools
 165.3K Java EE (Java Enterprise Edition)
 16 Java Essentials
 143 Java 8 Questions
 85.9K Java Programming
 79 Java Puzzle Ball
 65.1K New To Java
 1.7K Training / Learning / Certification
 13.8K Java HotSpot Virtual Machine
 94.2K Java SE
 13.8K Java Security
 197 Java User Groups
 24 JavaScript  Nashorn
 Programs
 243 LiveLabs
 35 Workshops
 10.2K Software
 6.7K Berkeley DB Family
 3.5K JHeadstart
 5.8K Other Languages
 2.3K Chinese
 166 Deutsche Oracle Community
 1.2K Español
 1.9K Japanese
 225 Portuguese
sq lquery to find specific values
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
Best Answers

@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

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

having

@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

@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.

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.

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).

I agree that oneword 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 plainEnglish explanation accordingly.

Can
product
benull?
If it can't, you should say so when you ask the question (and perhaps you should also have anot null
constraint in the table definition). If it can benull,
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), unlessproduct
can never benull,

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.
👍️