>Terry,
>JOIN act this way. If it see two or more values from one of the tables that match only one value from other the SELECT produces so many records as the max amount o fboth (I'm not sure If somebody understand that explanation, I didn't :o)))
>That is why the example comes:
>
>CREATE CURSOR crsTest1 (Fld1 int, Fld2 C(20))
>INSERT INTO crsTest1 VALUES(1, 'Test1_1')
>
>CREATE CURSOR crsTest2 (Fld1 int, Fld2 C(20))
>INSERT INTO crsTest2 VALUES(1, 'Test2_1')
>INSERT INTO crsTest2 VALUES(1, 'Test2_2')
>
>SELECT * FROM crsTest1 INNER JOIN crsTest2 ON crsTest1.Fld1 = crsTest2.Fld1
>
>
>And you will get a resultset with 2 records:
>
>1 Test1_1 1 Test2_1
>1 Test1_1 1 Test2_2
>
>
>I am not sure what you want to do, but give the example data from both tables and what you want as a final result.
Hi Boris
Thanks. It's impossible to send you sample data as there is a lot of building up cursors, from a mix of tables, selecting them into others, merging them with others, and so on, under several different criteria.
It's also very hard to explain. I understand your explanation but I thought left inner join took recs from LH table and only those that match them from the RH table. So how to avoid the duplicates was the essence of my question.
In the 2nd separate report, Total for stock item 1010104 = 513.99 but in the comparison, because it's including the pentuplicate rows the total comes to 2569.95, which is 5 times the real total, cos of the 4 extra rows in the cursor
Terry
- Whoever said that women are the weaker sex never tried to wrest the bedclothes off one in the middle of the night
- Worry is the interest you pay, in advance, for a loan that you may never need to take out.