SQL not selecting correctly - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL not selecting correctly

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2014-04-10 : 18:02:17

What I am trying to do is this.
table OEINHDIH is history of orders. Orders can have many order type rows. so that order 12345 can have types INT, COR etc within it.
we need the orders which are INT but don't have a correction order against it (COR COE). But when i run this sql i get only 1 ihent#. Also this has to be run in Crystal report where the user will drive this according to dates from and to which is h.IHDOCD.

select h.IHDOCD, h.IHENT#, h.IHSFX#, h.IHINV#, a.ADINTA
from astdta.OEINHDIH h
join astdta.Adressad a
on h.ihent# = a.ADENT#
where h.IHORDT = 'INT'
AND h.IHVIAC not in ('PML','FCM')
AND ADSFX# = '000'
and h.ihent# in (
select cor.ihent#
from astdta.OEINHDIH cor
where cor.IHORDT not in ('COR','COE'))

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-04-10 : 18:29:25

Try this:

select h.IHDOCD, h.IHENT#, h.IHSFX#, h.IHINV#, a.ADINTA 
from astdta.OEINHDIH h 
 join astdta.Adressad a 
 on h.ihent# = a.ADENT# 
where h.IHORDT = 'INT' 
 AND h.IHVIAC not in ('PML','FCM') 
 AND ADSFX# = '000' 
 and h.ihent# in not exists ( 
 select cor.ihent# 
 from astdta.OEINHDIH cor 
 where cor.IHORDT not in ('COR','COE') and cor.ihent#=h.ihent#)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources