Re: inner join SQL

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

I believe that, in any case, you need parentheses around the last 2 
conditions - all the ANDs are combined, then the OR starts a new set of 
conditions.

Not sure if that'll fix the problem - seems it won't, but it makes 
things right to start with, seems to me.

Also, just curious, what are the 4 records you get?

HTH
Vern

On 9/18/2013 12:42 PM, A Paul wrote:
> FILE1
> =====
>         
> ACCT brch TYC yrm flag QUANTITY positive negative
>               
>               
> ABCPQ 1    21 201312 2       645        0 645
> ABCPQ 1    25 201312 1        1920      1920 0
> ABCPQ 1    26 201312 2          192      0 192
> 1234C 1     25 201312 1         144      144  270
>   
>   
> FILE2
> =====
>         
> ACCT brch TYC yrm flag QUANTITY positive negative
>               
>               
> ABCPQ 1    21 201312 2        645   0       645
> ABCPQ 1    25 201312 1       1920  1920       0
> ABCPQ 1    26 201312 2        192  0         192
> 1234C 1    25 201312 1        144  144         0
> 1234C 1    25 201312 1         144  0         270
>   
> SELECT * FROM
> (SELECT ACCT,BRCH,TYC,YRM,FLAG FROM FILE2) T2
>                                 
>           INNER JOIN(
> SELECT
> ACCT, BRCH, TYC, YRM,FLAG,POSITIVE,NEGATIVE
> FROM FILE1
> ) T1
> ON T2.ACCT = T1.ACCT
> AND T2.BRCH = T1.BRCH
> AND T2.TYC = T1.TYC
> AND T2.YRM = T1.YRM
> AND (T2.POSITIVE = T1.POSITIVE) OR (T2.NEGATIVE = T1.NEGATIVE)
>   I am expecting to see my outfile from this SQL  have all 5 records from file2 ( they match with file1 on the conditions above), but  i am getting only 4records match.
> Can someone tell me what need to be fixed in my SQL to get all 5 records. I am having troule with record for account 1234C.
> For account 1234C I have two records and they both match to 1234C record in FILE1(if you see T2.positive = t1.POSITIVE AND T2.NEGATIVE = T1.NEGATIVE)