在mysql中连接两个表，给出重复的结果

Question

I am trying to connect 2 tables in mysql. But, I am getting duplicate values. This SQLFIDDLE will clear what I meant to say.

In result, you can see the TW_ID and PFC_ID. I just need (1, 1), (11, 11), (21, 21) and so on.. Right now I am getting (1, 1), (1, 11), (1, 21), ...

I am using this query:

SELECT tw.id as tw_id, pfc.id as pfc_id
FROM account_treasury_wholesale tw
INNER JOIN purchase_foreign_currency pfc
    ON tw.wholesaler_id = pfc.currency_supplier_id
WHERE tw.wholesaler_id='11'

Let me know, if you need more clarification. Thanks.

Answer 1

Either you JOIN ON tw.id=pfc.id, or if that's not applicable you may need to revise your schema.

This is what happens when you JOIN ON tw.wholesaler_id = pfc.currency_supplier_id:

1. The first row retrieved from the tw table is tw.id=1 (because of your WHERE clause)
2. That row's tw.wholesaler_id is 11, so it will look for rows in the pfc table for those with pfc.currency_supplier_id also equal to 11 -- which is 4 rows (id equal to 1, 11, 21, 23). So the cross product will be (1, 1), (1, 11), (1,21), (1,23)
3. The next row from the tw table is retrieved and the same search happens with the pfc table, which will lead to (11,1), (11,11), (11,21), (11,23)...

I admit I don't fully understand what you're trying to do. A little more explanation might help.