Inner Join

General
1

Hi group, I’m modifying the whole database which was badly done from the beginning… now I have a NominativiDitte table with the fields ID and NameCompany… and then another table IntestazioneFatturaAcquisti with the fields ID, ID_Nominativo,NumeroFattura,Date. … the second table refers to the first thanks to the ID NominativiDitte.ID=IntestazioneFatturaAcquisti.ID_Nominativo … I would need to connect the two tables and display the fields that have the ID in common. I tried writing:

rows2 = db.SelectSQL("SELECT ID,NomeDITTA from NominativiDitte INNER JOIN IntestazioneFatturaAcquisti ON IntestazioneFatturaAcquisti.ID_Nominativo=NominativiDitte.ID")

Exception: ambiguous column name: ID.

What is the problem ? I can rename column id ?

2

The problem is that the Column ID exists in both tables so you need to be explicit in your selection e.g.

rows2 = db.SelectSQL("SELECT NominativiDitte.ID,NomeDITTA from NominativiDitte INNER JOIN IntestazioneFatturaAcquisti ON IntestazioneFatturaAcquisti.ID_Nominativo=NominativiDitte.ID")
1 Like
3

Thanks Wayne. It’s WORK fine.

4

Ok Wayne, let me just ask one question. With the INNER JOIN, I can select two different tables joined by an ID, and how I recall a field from a table… because it tells me that the field does not exist, but in the second table of the INNER JOIN the field exists. Do I have to enter something like * to recall all the fields?

5

You can use something like table1., table2. to see the contents of both tables.

6

about IDs i like this style:

table name singular Company
id field CompanyId

it is common to join data together, you not have a conflict and know what the id is.

useing underscore is matter of taste.