Home » SQL & PL/SQL » SQL & PL/SQL » AND criteria in where clause
AND criteria in where clause [message #658244] Wed, 07 December 2016 08:40 Go to next message
blyzz
Messages: 10
Registered: October 2015
Junior Member
I have a table for students with code and description in 2 columns
name code des
test1 A2 High Grade
test1 B1 Avg Math Grade
test2 B3 Avg Math Grade

I want to specify the "and" criteria in where clause to get students who got grades betwen A1-A4 and B1-B4 grade


select * from tbl where code between A1-A4 and code between B1-B4
I am getting No value but it should return 2 records for test1
but if I use OR I am getting both the record.

The issue is I want to select the values when both the conditions are met.
How can I do that?

Thanks,
blyzz
Re: AND criteria in where clause [message #658246 is a reply to message #658244] Wed, 07 December 2016 08:44 Go to previous messageGo to next message
BlackSwan
Messages: 25859
Registered: January 2009
Location: SoCal
Senior Member
>select * from tbl where code between A1-A4 and code between B1-B4
>I am getting No value but it should return 2 records for test1

explain how a SINGLE code value can at the SAME time be between A1 & A4 AND between B1 & B4
code will either be between A1 & A4 OR be between B1 & B4
Re: AND criteria in where clause [message #658251 is a reply to message #658246] Wed, 07 December 2016 09:14 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use INTERSECT
Re: AND criteria in where clause [message #658260 is a reply to message #658244] Wed, 07 December 2016 10:56 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Align the columns in result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

Re: AND criteria in where clause [message #658385 is a reply to message #658260] Mon, 12 December 2016 06:54 Go to previous messageGo to next message
Bill B
Messages: 1718
Registered: December 2004
Senior Member
You can also do a self join

select a.name
from my_table b,my_table a
where a.name = b.name
and a.code between 'A1' AND 'A4'
AND B.CODE BETWEEN 'B1' AND 'B4'
GROUP BY a.name;
Re: AND criteria in where clause [message #658387 is a reply to message #658385] Mon, 12 December 2016 07:20 Go to previous message
Solomon Yakobson
Messages: 2711
Registered: January 2010
Location: Connecticut, USA
Senior Member
Self join (or intersect as it was suggested earlier) will work but it inefficient. OP should use analytics. Something like:

with t as (
           select  name,
                   sum(
                       distinct case
                                  when code between 'A1' AND 'A4' then 1
                                  when code between 'B1' AND 'B4' then 2
                                  else 4
                                end
                      ) over(partition by name) flag,
                   row_number() over(partition by name order by 1) rn
             from  my_table
          )
select  name
  from  t
  where flag = 3
    and rn = 1
/


SY.

[Updated on: Mon, 12 December 2016 07:23]

Report message to a moderator

Previous Topic: Package Specification varaible
Next Topic: Help in Listagg Query???
Goto Forum:
  


Current Time: Sun Feb 25 22:13:40 CST 2018

Total time taken to generate the page: 0.01183 seconds