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.
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:
tw table is tw.id=1 (because of your WHERE clause)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)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.