What is wrong here

[sbv]

Hi
i want to select the values from my table where a column must contains some value. So i wrote this
Select * from RG_TAB where CMCode = 4 and CMCode = 5

This query not giving me any result but

Select * from RG_TAB where CMCode = 4 Or CMCode = 5

is giving me result.
I am confused. What is happening. What should be the query?

Thanks and regards,
sbv

[Jx_Man]

Select * from RG_TAB where CMCode = 4 and CMCode = 5

This query not giving me any result but

if u use this clause (and operator) it means two condition must be available. if just one condition only (ex CMCode=4) then this command didn't give result. so CMcode = 4 & CMcode = 5 must be available to get result.

Select * from RG_TAB where CMCode = 4 Or CMCode = 5 is giving me result.

This clause will give result if one of condition available or two condition available. so you don't needed two condition to give a result, one condition can give result.

[sbv]

if u use this clause (and operator) it means two condition must be available. if just one condition only (ex CMCode=4) then this command didn't give result. so CMcode = 4 & CMcode = 5 must be available to get result.

This clause will give result if one of condition available or two condition available. so you don't needed two condition to give a result, one condition can give result.

Hi
Thanks for reply.
My Table contents are as follows.....

RID MasterVal CMCode
-------------------------------------
1 Cricket 4
2 Marketing 5
3 bla bla

Now here from my table i want to select records where CMCode is 4 and 5. So is my query is wrong!! :(

[jwenting]

yes it is, use "where cmcode = 4 or cmcode = 5" which will give you all the rows where cmcode is either 4 or 5 (it can never be both at the same time, which is why your original query gave no results).

[Sawamura]

yes, as jwenting said. it can never be both at the same time.

[kevs1607]

Hi i want to select the values from my table where a column must contains some value. So i wrote this Select * from RG_TAB where CMCode = 4 and CMCode = 5

This query not giving me any result but

Select * from RG_TAB where CMCode = 4 Or CMCode = 5

is giving me result. I am confused. What is happening. What should be the query?

Thanks and regards, sbv

hi
use this query - Select * from RG_TAB where CMCode = 4 Or CMCode = 5

[Jx_Man]

i suggest to use or operand in query, don't use and.

[Nige Ridd]

An alternative which you may see commonly is instead of using multiple references to a field with 'or' is to write something like

Select * from RG_TAB where CMCode in ( 4, 5 )

You can imagine when you have 5 or 6 possible values that this is much shorter and easier to read.
Nige

[sbv]

An alternative which you may see commonly is instead of using multiple references to a field with 'or' is to write something like

Select * from RG_TAB where CMCode in ( 4, 5 )

You can imagine when you have 5 or 6 possible values that this is much shorter and easier to read. Nige

hi
thanks to all.
what i need is gained by using the query of IN and Not In.

[debasisdas]

AND means all the conditon must satisfy.

OR means any of the condition should satisfy.