Re: Classic trap : using SQL while not fully competent

David FOXWELL wrote:

I was going to cry for help :

I know I have a record on the left that ain't on the right so I did

SELECT * FROM prdcap exception join adhtst on ad1cad=tadhno
WHERE tcrtcd ='14'

Which returned no record.

Since tcrtcd is in the right-hand table, this technically doesn't even make sense. By definition, you're saying you want to create a result set on rows that have no matching data in the right-hand table. Any such row would have data in the left-hand fields and nulls in the right-hand fields, so the WHERE clause will never match. I'm not sure why this gets past the syntax checker; maybe one of the big guns can help me.

But

SELECT * FROM prdcap WHERE P ad1cad not in
( select tadhno from adhtst where tcrtcd ='14')

Found the record.

After a while I thought, maybe its because tcrtcd is in the right hand table, so I tried
SELECT * FROM prdcap exception join adhtst on ad1cad=tadhno
and tcrtcd = '14'

Which worked.

Its the first time I put the where criteria in the JOIN. Can anyone give me the technical explication as to why the first example didn't work so I don't fall in again please ?

This isn't WHERE criteria, because you're not using tcrtcd = '14' to limit the number of rows to return. You're using that comparison to determine whether a match is found, so it must be part of the exception join.

Joe