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")

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

iOS app development

The Future of iOS App Development: Trends to Watch

When it launched in 2008, the Apple App Store only had 500 apps available. By the first quarter of 2022, the store had about 2.18 million iOS-exclusive apps. Average monthly app releases for the platform reached 34,000 in the first half of 2022, indicating rapid growth in iOS app development.

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a