JOIN 2 tables from OpenERP & iReport

[marciokoko]

Im working with iReport and Ive connected to the db of openERP.

I already pulled data from the invoices table, but it contains a partner_id which is the client name.  I need to get the client name from the res_partner table.  So i think I have to use a join, right?  So I did this:

select * from account_invoice JOIN res_partner ON account_invoice.partner_id = res_partner.id

The problem is that I am not getting the res_partner.name field available from the left pane to drag over to the report designer view.  I only get the account_invoice.partnerid which is no good to me.

If I do select * from res_partner, the res_partner.name field is there, but I need the data joined to the invoices table.

What am I doing wrong?

[pioppogatto]

Did you try to check 'Automatically retrive the fields'? Does the field appear in the field list below the report query? Sometimes you need to refresh your query in oder fields appear in your report inspector

[marciokoko]

Ok I opened up the report again and I clicked on Read Fields, this time it did display both name fields, I checked.  But it wont import them to the report designer because it turns out both name and id are duplicate fields, so I get a duplicate fields error, for now, for the id field.  I guess the same thing will happen for the name field once i resolve the id duplicate. 

My question is, how do i resolve that duplicate issue?

[pioppogatto]

Ok, try to put an alias to yout table query and put another alias to the duplicate field. Exemples

select A.id as ID_A, B.ID as ID_B from account_invoice A JOIN res_partner B ON A.partner_id =B.id

Try to put in the "select" only the fields that you need and do not use *

Let me know

Antonio