devxlogo

Use aliased field names to avoid ambiguities in JOIN commands

Use aliased field names to avoid ambiguities in JOIN commands

Suppose you have two tables named Table1 and Table2 with one or more fields named the same way. Now, consider this query:

rs.Open "SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id"

In our example, both table1 and table2 share the same field name “field1”, but with different content. Our problem is how to reference each field so to gain access to its value. The statement:

? rs("field1")

is ambiguous because it might refer both to table1 or table2; usually it returns the field value related to last joined table (table2 in the example), but it’s not guaranteed and might depend on the specific OLE DB providers and other factors. Specifying the table name – such as rs(“table1.field1”) – returns an error because ADO doesn’t store field names with their table path, so using the following query doesn’t help:

rs.Open "SELECT table1.*, table2.* FROM table1 INNER JOIN table2 ON table1.id = " _    & "table2.id"

To solve this problem you must use aliases for field names. This method forces you to specify all the ambiguous fields:

rs.Open "SELECT table1.field1 as Name, table2.field1 as Surname, * FROM table1 " _    & "INNER JOIN table2 ON table1.id = table2.id"

Now you can refer to any field by using the alias instead of real name:

? rs("Name"), rs("Surname")

See also  5 Ways to Improve Customer Experience
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist