CODEWITHSUNDEEP
phpsqlsql-serverselectjoin
joebegborg07
joebegborg07

Reputation: 839

PHP - error when joining fields from different SQL databases

I am trying to build a query where I return results from 3 different tables as per the following:

Events Table DateTime, Direction, DeviceName

Tenants TenantName

Individuals FirstName, LastName

Initially I've returned fields from the first 2 tables with the following SQL: 

SELECT EventTime, DeviceName, Comment, TenantName
FROM taclogdata.dbo.Event 
     INNER JOIN InetDb.dbo.Tenants 
       ON taclogdata.dbo.Event.Tenant = InetDb.dbo.Tenants.TenantId
WHERE taclogdata.dbo.Event.EventTime 
       BETWEEN '01/04/2014 16:00:00' AND '01/04/2014 16:00:59

I've joined both the Event and Tenants tables using the TenantId Field.

I then needed to also return the fields FirstName and LastName (from the Individuals table), so I tried using the sql below:

    SELECT EventTime, DeviceName, Comment, TenantName, FirstName, LastName 
    FROM taclogdata.dbo.Event 
    INNER JOIN InetDb.dbo.Tenants 
       ON taclogdata.dbo.Event.Tenant = InetDb.dbo.Tenants.TenantId
    INNER JOIN InetDb.dbo.Tenants
       ON inet.dbo.Tenants.TenantId = inet.dbo.Individuals.TenantNdx
    WHERE taclogdata.dbo.Event.EventTime 
          BETWEEN '01/04/2014 16:00:00' AND '01/04/2014 16:00:59'

However this returned the following error:

The objects "InetDb.dbo.Tenants" and "InetDb.dbo.Tenants" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

Can someone please guide me on how it's best to resolve this please ?

UPDATED

I've modified the syntax to the below:

SELECT EventTime, DeviceName, Comment, TenantName, FirstName, LastName 
FROM taclogdata.dbo.Event 
INNER JOIN InetDb.dbo.Tenants 
    ON taclogdata.dbo.Event.Tenant = InetDb.dbo.Tenants.TenantId
INNER JOIN InetDb.dbo.Individuals
    ON  InetDb.dbo.Tenants.TenantId = InetDb.dbo.Individuals.TenantNdx
WHERE taclogdata.dbo.Event.EventTime 
      BETWEEN '01/04/2014 16:00:00' AND '01/04/2014 16:00:59'

I then received the following error:

Msg 209, Level 16, State 1, Line 1 Ambiguous column name 'FirstName'. Msg 209, Level 16, State 1, Line 1 Ambiguous column name 'LastName'

Upvotes: 0

Views: 48

Answers (1)

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

You are missing Individuals table in INNER JOIN and use Tenants by mistake.

SELECT EventTime, DeviceName, Comment, TenantName,        
       InetDb.dbo.Individuals.FirstName, InetDb.dbo.Individuals.LastName 
FROM taclogdata.dbo.Event 
INNER JOIN InetDb.dbo.Tenants 
    ON taclogdata.dbo.Event.Tenant = InetDb.dbo.Tenants.TenantId
INNER JOIN InetDb.dbo.Individuals
    ON  InetDb.dbo.Tenants.TenantId = InetDb.dbo.Individuals.TenantNdx
WHERE taclogdata.dbo.Event.EventTime 
       BETWEEN '01/04/2014 16:00:00' AND '01/04/2014 16:00:59'

Upvotes: 1

Related Questions