Hi,

I have a two tables join, when i run the report i am getting the same results repeated over and over. Table A is Join to Table B.

What am i doing wrong?

This message has been edited. Last edited by: Kerry,

Could you provide more informations about your code and your data?

This is a simple join:

  

TABLE1
-----------
001 AAA
002 BBB
003 CCC


TABLE2
-----------
001 XXX 
003 FFF

JOIN CLEAR *
JOIN KEY IN TABLE1 TO KEY IN TABLE2 AS J1

You get:

001 AAA 001 XXX
002 BBB
003 CCC 003 FFF

hi,

This is my code.

JOIN
FCT_PURCH.FCT_PURCH.SUPPLIER_KEY IN FCT_PURCH TO MULTIPLE
DIM_SUPPLIER.DIM_SUPPLIER.SUPPLIER_KEY IN DIM_SUPPLIER TAG J0 AS J0
END

This is the results i am getting.

04258 THOMAS & BETTS LTD *EDI* 3 38283 6 BRUNO CARBONE $105,256.99 2009/06/29
04258 THOMAS & BETTS LTD *EDI* 3 38283 6 BRUNO CARBONE $105,256.99 2009/06/29
04258 THOMAS & BETTS LTD *EDI* 3 38283 6 BRUNO CARBONE $105,256.99 2009/06/29
04258 THOMAS & BETTS LTD *EDI* 3 38283 6 BRUNO CARBONE $105,256.99 2009/06/29

Is 04258 the key value?

Is this value one time in table FCT_PURCH and 4 times in table SUPPLIER_KEY?

Hi,

Yes, its DIM_SUPPLIER

That's why you're getting multiple values you've joined one record in FCT_PURCH "TO MULTIPLE" records in DIM_SUPPLIER. So you get all the records you've joined to. It's called the multiplicative effect. If you only want one line, you've either got to use a SUM or remove the "TO MULTIPLE." If you don't know what the "TO MULTIPLE" is doing in your Join, I would refer you back to the basic documentation.

Amit,

You also have not told us which fields you are showing in your report - are they originating from both tables or from just one? Are the keyfield(s) of both tables in the report? If not, then include them and you would have to see different field values for the keys in the second table. This is of course assuming that the join-to file has keys defined in ms sql. If no keys are defined, then duplicate values may occur, but then they will still have to be present in the join-to table.