CODEWITHSUNDEEP
sqlms-access
brainac
brainac

Reputation: 410

Column alias conflict with table names in MS Access SQL

I work on MS Access 2010 database and I stumbled upon weird issue with column aliases. I have a query with multiple joins, and I want to name my result columns. However, some aliases result in empty columns.

In my efforts to understand this issue and isolate it, I prepared a query that should display the same column with different aliases, with each alias being a name of existing table or query in my database. Funny enough, 3 of them result in empty columns, (AS [Analyst], AS [MeetingCompany], and AS [Sector]), while the others work fine. Note, that Analyst table isn't even in this query, but exists in the database.

Is there any rule for column aliases that I'm not aware of? Or is it just an oddity I have to live with and use different aliases?

SELECT  Sector.SectorName AS [Analyst], Sector.SectorName AS [vCompanyPrimary], 
Sector.SectorName AS [MeetingCompany], Sector.SectorName AS [Client],
Sector.SectorName AS [ClientContact], Sector.SectorName AS [MeetingClientContact], 
Sector.SectorName AS [Sector], Sector.SectorName AS [vMeetingSectorID], 
Sector.SectorName AS [EventTypeGroup], Sector.SectorName AS [Country], 
Sector.SectorName AS [Area], Sector.SectorName AS [City]
FROM (((((((((((Meeting INNER JOIN Event ON Meeting.EventID = Event.EventID)
 INNER JOIN EventType ON Event.EventTypeID = EventType.ID)
 LEFT JOIN vMeetingSectorID ON Meeting.MeetingID = vMeetingSectorID.MeetingID)
 LEFT JOIN Sector  ON vMeetingSectorID.SectorID = Sector.ID)
 LEFT JOIN City ON City.ID = Meeting.CityID)
 LEFT JOIN Area ON City.AreaID = Area.ID)
 LEFT JOIN Country ON Country.ID = City.CountryID)
 LEFT JOIN EventTypeGroup ON EventType.ID = EventTypeGroup.EventTypeID)
 LEFT JOIN MeetingClientContact ON Meeting.MeetingID = MeetingClientContact.MeetingID)
 LEFT JOIN ClientContact ON MeetingClientContact.ClientContactID = ClientContact.ContactID)
 LEFT JOIN Client ON ClientContact.ClientID = Client.ClientID)

EDIT: I managed to find and remove a culprit, but I still don't understand the rules for this limitation. vMeetingSectorID is a saved query in Access, and it used tables Analyst, Sector, and MeetingCompany. Once I used aliases for table names in this saved query, all columns are displayed properly. I will be greatful for any hint, so I can avoid these situations in future.

Upvotes: 1

Views: 701

Answers (1)

Parfait
Parfait

Reputation: 107567

As you found, you are running into name clashes which can have unexpected results. To avoid in future always use table aliases especially with self-joins and joining queries with same underlying tables. Specifically, the embedded query:

LEFT JOIN vMeetingSectorID 
   ON Meeting.MeetingID = vMeetingSectorID.MeetingID

Is arguably equivalent to a subquery or derived table:

LEFT JOIN (SELECT * FROM ... INNER JOIN ...) AS vMeetingSectorID 
  ON Meeting.MeetingID = vMeetingSectorID.MeetingID

And this inner query uses same tables as outer query such as Sector. The engine can then have issues associating identifiers especially with a resulting column named, Sector, as a table.

Therefore, use table aliases that properly defines the inner and outer query scopes. Plus it makes long queries more compact and readable as shown below. Do the same of course with the query, vMeetingSectorID.

SELECT  s.SectorName AS [Analyst], s.SectorName AS [vCompanyPrimary], 
        s.SectorName AS [MeetingCompany], s.SectorName AS [Client],
        s.SectorName AS [ClientContact], s.SectorName AS [MeetingClientContact], 
        s.SectorName AS [Sector], s.SectorName AS [vMeetingSectorID], 
        s.SectorName AS [EventTypeGroup], s.SectorName AS [cnty.], 
        s.SectorName AS [Area], s.SectorName AS [City]
FROM (((((((((((Meeting m INNER JOIN Event e ON m.EventID = e.EventID)
INNER JOIN EventType et. ON e.EventTypeID = et.ID)
LEFT JOIN vMeetingSectorID v ON m.MeetingID = v.MeetingID)
LEFT JOIN Sector s ON v.SectorID = s.ID)
LEFT JOIN City cy ON cy.ID = m.CityID)
LEFT JOIN Area a ON cy.AreaID = a.ID)
LEFT JOIN Country n ON n.ID = cy.ID)
LEFT JOIN EventTypeGroup etg ON et.ID = etg.EventTypeID)
LEFT JOIN MeetingClientContact mcc ON m.MeetingID = mcc.MeetingID)
LEFT JOIN ClientContact cc ON mcc.ClientContactID = cc.ContactID)
LEFT JOIN Client c ON cc.ClientID = c.ClientID)

Upvotes: 1

Related Questions