Where clause not properly excluding records in one-to-many join query

[mjmabee]

I am trying to write a query that will select records from a primary table when they meet two criteria conditions in that table and their associated records in the foreign key table match ONLY to one of the three values. The foreign key table can contain up to three records matching back to the one in the primary table. In many situations, one of these three records (in the foreign key table) does contain one of the values I'm looking for, but it also contains records with values that should exclude the record (across all tables) from my result set.

The query below is my best guess as to how to retrieve this result set. The part that's giving problems is in CAPS.




select i.amt, e.code

from (dbo.hExceptionitem ei join dbo.hItem i on (ei.batchnumber = i.batchnumber) and (ei.processingdate = i.processingdate) and (ei.itemrow = i.rownumber) join dbo.hException e on (ei.processingdate = e.processingdate) and (ei.batchnumber = e.batchnumber) and (ei.exceptionrow = e.rownumber))

where i.date='11/1/2006' and (substr(i.num,1,3)='123') and (e.code in ('18','1018','7018')) AND (E.CODE NOT BETWEEN '1' AND '17' AND E.CODE NOT BETWEEN '19' AND '75') AND (E.CODE NOT BETWEEN '1001' AND '1017' AND E.CODE NOT BETWEEN '1019' AND '1075') AND (E.CODE NOT BETWEEN '7001' AND '7017' AND E.CODE NOT BETWEEN '7019' AND '7075')




When I run this query without the part in caps, I do get some records that, legitimately, should not be there. The trouble is, it also returns some records that SHOULD be there that are not returned with the query shown above.... records, that is, that ONLY have an e.code field value of '1018'.

I have tried tons of different permutations of this query. Each time i either wind up missing some records that i know should be there, or i get records in the result set that should not be there.

If anyone has any suggestions, please let me know. Any help is greatly appreciated.

 

[mjmabee]

Sorry to take so long getting back, I have had a busy day so far ! Marc, the recordsets from both of your queries were identical. They include:

1st Record:

i.id:1DDA610116088299.0000000000004105489154 i.amt:299.00
i.batchnumber:8826
i.processingdate:11/01/2006
i.rownumber:2612
substr(i.num,1,3):610
e.code:1018

2nd Record:

i.id:1DDA61013656950000.0000000000004126203447 i.amt:50000.00
i.batchnumber:8826
i.processingdate:11/01/2006
i.rownumber:2639
substr(i.num,1,3):610
e.code:1018

3rd Record:

i.id:1DDA6101436625000.0000000000000040705658 i.amt:5000.00
i.batchnumber:8826
i.processingdate:11/01/2006
i.rownumber:2643
substr(i.num,1,3):610
e.code:18

4th Record:

i.id:1DDA6101436625000.0000000000005607227444 i.amt:5000.00
i.batchnumber:8826
i.processingdate:11/01/2006
i.rownumber:2644
substr(i.num,1,3):610
e.code:1018

Unfortunately, this result set still includes records it should not. The first record in the set, in addition to having a record in the dbo.hException table with e.code equal to '1018', has another record where the value is '1007'.

I tried my query too, and i get an error saying: a.itemrow is not valid in the context where it is used.

I think the key to the subquery (aside, of course, from tying it back to the main query) is to use it to select a count of the dbo.hExceptionitem.exceptionrow field. If the subquery returns a count 1, then the item record selected by the main query should only have a code of '18','1018', or '7018' - the presence of a row value in the exceptionrow field indicates that there is an excpetion associated with the item referred to by dbo.hExceptionitem.itemrow.

I'm still not sure how this needs to be written, though.

 

[ddiamond]

I'm confused by the same thing that kkitt mentioned. If you include the condition e.code IN ('18','1018','7018'), then you have limited your output to only records with that exception code, so your between clauses become irrelevent (i.e. they should have no effect on your output.) So when you ran the query without the between clauses, what records are you getting that you shouldn't?

SELECT i.amt, e.code
FROM dbo.hExceptionitem ei 
  JOIN dbo.hItem i ON 
    ei.batchnumber = i.batchnumber AND 
    ei.processingdate = i.processingdate AND 
    ei.itemrow = i.rownumber 
  JOIN dbo.hException e ON 
    ei.batchnumber = e.batchnumber AND 
    ei.processingdate = e.processingdate AND 
    ei.exceptionrow = e.rownumber
WHERE i.date='11/1/2006' 
  AND SUBSTR(i.num,1,3)='123' 
  AND e.code IN ('18','1018','7018')

 

[mjmabee]

Your wording "then you have limited your output to only records with that exception code" is exactly right. e.code IN ('18','1018','7018') DOES limit OUTPUT to only those records that have an '18','1018', or '7018' in the e.code field. The problem is that some of the records that are included in the output, that display these codes in the output also have other associated e.code values.

In cases where an item has multiple associated entries in the dbo.hException table (instead of having ONLY[/] an '18','1018' or '7018'), the item itself should be excluded from the result set. Does that make sense ?

 

[mjmabee]

Although i know there's a problem with this query:

select i.id
, e.code

from dbo.hExceptionitem ei
,dbo.hItem i
, dbo.hException e

Where ei.batchnumber = i.batchnumber
and ei.processingdate = i.processingdate
and ei.itemrow = i.rownumber

and ei.processingdate = e.processingdate
and ei.batchnumber = e.batchnumber
and ei.exceptionrow = e.rownumber

and i.date='11/1/2006'
and substr(i.num,1,3)='610'
and e.code in ('18','1018','7018')

and 1 = (select count(a.exceptionrow)

from dbo.hExceptionitem a
, dbo.hItem b

Where a.batchnumber = b.batchnumber
and a.processingdate = b.processingdate
and a.itemrow = b.rownumber
and b.id = i.id)

I still can't help but think it's close... shouldn't the i.id tie it back to the main query ? Extending on that, shouldn't the subquery then be able to match records between the hExceptionitem table and the hItem table given the unique id value ? Once that has been done, we just need the subquery to tell us how many exceptionrow values it has associated with that item. If the answer is one, that item should be included in output. If not, it should be discarded from the result set.

 

[mjmabee]

I figured it out !

Here it is:

select i.id, e.code

from dbo.hExceptionitem ei, dbo.hItem i, dbo.hException e

where ei.batchnumber = i.batchnumber
and ei.processingdate = i.processingdate
and ei.itemrow = i.rownumber

and ei.processingdate = e.processingdate
and ei.batchnumber = e.batchnumber
and ei.exceptionrow = e.rownumber

and i.processingdate='11/1/2006'
and substr(i.num,1,3)='610'
and e.code in ('18','1018','7018')
and 1 = (select count(a.exceptionrow)

from dbo.hExceptionitem a

where a.batchnumber = ei.batchnumber
and a.processingdate = ei.processingdate
and a.itemrow = ei.itemrow)

Thanks to everyone for your help, and for the discussion.