Using AND

Question

Using AND

kapil_kk

SSC-Insane

Points: 21316
More actions
June 4, 2013 at 3:20 am

#278447

Hi,
I am confused with a query that:
SELECT STNNo FROM table1 where VoucherBookletNo = 'VVB0000021' AND VoucherBookletNo ='VVB0000025'
When I run this query it return nothing. WHy?
TableName:
transactionID, StoreID, STNNo, VoucherBookletNo, Quantity, AllocatedDate
TableData:
110041004000032VVB0000021162013-06-04 11:11:19.787
210041004000032VVB0000022162013-06-04 11:11:19.787
310041004000032VVB0000023162013-06-04 11:11:19.787
410041004000032VVB0000024162013-06-04 11:11:19.787
510041004000032VVB0000025162013-06-04 11:11:19.787
610041004000032VVB0000026162013-06-04 11:11:19.787
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

BrainDonor SSCoach Points: 19252 More actions · Answer 1

kapil_kk (6/4/2013)
Hi,
I am confused with a query that:
SELECT STNNo FROM table1 where VoucherBookletNo = 'VVB0000021' AND VoucherBookletNo ='VVB0000025'
When I run this query it return nothing. WHy?

Because VoucherBookletNo has to be 'VVBB0000021' AND 'VVBB0000025' at the same time.

If you specify OR then it can be one value or the other and will work.

Steve Hall
Linkedin
Blog Site

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

You are probably looking for this, a scalar valued column cannot contain 2 different values in a single row

SELECT STNNo FROM table1 where VoucherBookletNo = 'VVB0000021' OR VoucherBookletNo ='VVB0000025'

-- OR

SELECT STNNo FROM table1 where VoucherBookletNo in ( 'VVB0000021', 'VVB0000025' )

Edit:Added one more option

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

Kingston Dhasian (6/4/2013)
You are probably looking for this, a scalar valued column cannot contain 2 different values in a single row
SELECT STNNo FROM table1 where VoucherBookletNo = 'VVB0000021' OR VoucherBookletNo ='VVB0000025'
-- OR
SELECT STNNo FROM table1 where VoucherBookletNo in ( 'VVB0000021', 'VVB0000025' )
Edit:Added one more option

Yes, I tried this already and these queries will return 2 rows for 2 values....

But I just want to clear doubt that if a single column contain 2 different values in two different rows then how AND works in that condition... why my query returned nothing

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 4

Well. That is how AND works.

SELECT STNNo FROM table1 where VoucherBookletNo = 'VVB0000021' AND VoucherBookletNo ='VVB0000025'

If your query is translated in simple English, it will be something like

Give me the row where VoucherBookletNo is "VVB0000021" and "VVB0000025"

A single row cannot satisfy such a condition and hence, you are not getting any rows.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

Kingston Dhasian (6/4/2013)
Well. That is how AND works.
SELECT STNNo FROM table1 where VoucherBookletNo = 'VVB0000021' AND VoucherBookletNo ='VVB0000025'
If your query is translated in simple English, it will be something like
Give me the row where VoucherBookletNo is "VVB0000021" and "VVB0000025"
A single row cannot satisfy such a condition and hence, you are not getting any rows.

Ok thanks 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

kapil_kk (6/4/2013)
But I just want to clear doubt that if a single column contain 2 different values in two different rows then how AND works in that condition... why my query returned nothing

If you want to return the two rows that each have one of those, you want OR.

Think of it this way.

AND: Get me all the books off the bookshelf where the book title is both "Lord of the Rings" and "Total Recall"

0 books qualify for that, because no such book whose title that is both of those values at the same time

OR: Get me all the books off the bookshelf where the title is either "Lord of the Rings" or "Total Recall"

2 books qualify, one with the title "Lord of the Rings", one with the title "Total Recall"

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kapil_kk SSC-Insane Points: 21316 More actions · Answer 7

GilaMonster (6/4/2013)
kapil_kk (6/4/2013)
But I just want to clear doubt that if a single column contain 2 different values in two different rows then how AND works in that condition... why my query returned nothing
If you want to return the two rows that each have one of those, you want OR.
Think of it this way.
AND: Get me all the books off the bookshelf where the book title is both "Lord of the Rings" and "Total Recall"
0 books qualify for that, because no such book whose title that is both of those values at the same time
OR: Get me all the books off the bookshelf where the title is either "Lord of the Rings" or "Total Recall"
2 books qualify, one with the title "Lord of the Rings", one with the title "Total Recall"

Thanks Gail...

now its clear to me 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Ramon Jimenez Default port Points: 1467 More actions · Answer 8

Also if you want to get two rows you can use the IN statement

SELECT STNNo FROM table1 where VoucherBookletNo IN ('VVB0000021','VVB0000025')

Regards Ramon