SQL statement "IN"

Question

I have a doubt asking for a select statement with an in statement. That is, if I need to get multiple po values ​​according to their zeros. 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 one point. suppose if I give so

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

(One PO may have a multi-digit number. There is nothing unique)

how it works? be it 1. it returns a row in which there are no 001 available or 2.it returns a row corresponding to pono = 82200388180, with dueno = 001.

I need to get an answer as option 2. please help me

Thanks in advance.

Answer 1

How can I get the correct po values ​​according to the corresponding relevant no.

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

This will probably give you the result you want.

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

Alternatively, you can have a table / temp table / table variable with pairs of values ​​to check for the use of 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 using PostgreSQL, you can use this instead:

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

Answer 2

Usually you 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 directly. There is no difference (or advantage) in performing

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

compared with

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