SQL "IN" Operator

Question

I have a doubt with the select statement query with in operator. That is, if I need to get the multiple po values with according to their due nos. If I do this,

select * from table1 where pono in('82200388180','82200388179') and dueno in('001','004')

then it works. But I need to clarify with you about one point. assume if i giving like this,

select * from table1 where pono in('82200388180','82200388179') and dueno ('001','004')

(One PO can have Multiple Due Nos. Nothing is unique)

how it works? whether 1. it returns the row where due no 001 available or 2.it returns the row corresponding to the pono=82200388180 with the dueno=001.

i need to get the answer like option 2. please guide me

Thanks in advance.

If I understood you correctly, you want to delete a record where `pono` can be `1 and 2` but the dueno should be equal to `001`? — John Woo, May 24 '13 at 06:04
I'm not sure what exactly you're asking for. The second query looks just fine. It should still work even if the numbers are both the same - it will still only return rows from table1 with the given dueno-set (even though the set only contains "001") — Taryn East, May 24 '13 at 06:04
Give an example of input and output you want. Your question is not clear. — Dhwani, May 24 '13 at 06:08
What relationship is there between *pono* and *dueno*? one *pono* corresponds to single dueno(ie. one-to-one mapping) or may it one-to-many? please clarify — Sachin, May 24 '13 at 06:08
I think you should union both two tables to get correct result. — Kadir, May 24 '13 at 06:09
@Taryn East: ya i agree with you friend. It will return a row. Assume if i have a table with the values like PONo: 1 ,dueno :001,002 and PONo: 2, DueNo:001,003.. Here i need to get the record of PONo1 where the dueno 001 also PONO2 where 001.. Here we may get a conflict right?? — user2393614, May 24 '13 at 06:11

Mikael Eriksson · Answer 1 · 2013-05-24T07:23:37.323

How can I get the proper po values according to that corresponding due no.

There is no correlation between the positions in the in clause.

This will probably give you result you want.

select *
from table1 
where pono = '1' and dueno = '001' or
      pono = '2' and dueno = '001'

Alternatively you could have a table/temp table/ table variable with the value pairs to check against using exists or if you are in SQL Server 2008 you can do something like this.

select *
from table1 as T1
where exists ( 
             select *
             from (values('1','001'),
                         ('2','001')) as T(pono, dueno)
             where T1.pono = T.pono and
                   T1.dueno = T.dueno
             )

If you are on PostgreSQL you could use this instead:

select *
from table1
where (pono, dueno) in (('1', '001'), ('2', '001'))

@DavidAldridge Sure they would not hurt and they would clarify what the where clause does but if you put them in the correct positions they will not change the output since `and` has higher precedence than `or`. — Mikael Eriksson, May 24 '13 at 07:30

score 0 · Answer 2 · answered May 24 '13 at 06:08

0

Normally you'd do something like this:

select * from table1 where pono in('1','2') and dueno = pono

But in your case pono and dueno are different formats so you cannot compare the directly. There is no difference (or advantage) in doing

select * from table1 where pono in('1','2') and dueno in('001','001')

compared to

select * from table1 where pono in('1','2') and dueno ='001'

answered May 24 '13 at 06:08

Old Pro

24,624
7
58
106

but i should match the multiple pono's with multiple dueno's friend.. What should i do for that – user2393614 May 24 '13 at 06:20
1

Then I really do not understand what you are asking. Please edit the question to provide an example table of data and show the results you are looking for. – Old Pro May 24 '13 at 06:21
i have modified my question.. Please see that and let me know friend – user2393614 May 24 '13 at 06:47
You did not provide example data or the desired output. See [this answer](http://stackoverflow.com/a/12401086/712765) to another question for an example of how to clearly explain using example data and output. – Old Pro May 24 '13 at 06:52

SQL "IN" Operator

2 Answers2

Linked