CODEWITHSUNDEEP
sqlpowershell
user3078335
user3078335

Reputation: 781

Getting an error after running join

I keep getting this error anytime i try to run the sql statement. What could be wrong?

Ambiguous column name 'trafficCrashReportId'.

Upvotes: 0

Views: 35

Answers (3)

The_Black_Smurf
The_Black_Smurf

Reputation: 5269

The trafficCrashReportId is present in both tables (InvolvedVehicle and TrafficCrashReport)

You'll need to specify which one should be selected... This example shows how to select the trafficCrashReportId from the InvolvedVehicle table.

Invoke-Sqlcmd -Query "select [trafficCrashReportId],
       InvolvedVehicle.involvedVehicleId,
      ,[assocVehNum]
      ,[wasStrikingVehicle]
from InvolvedVehicle INNER JOIN TrafficCrashReport ON InvolvedVehicle.trafficCrashReportId = TrafficCrashReport.trafficCrashReportId ...

Upvotes: 3

Ryan Picou
Ryan Picou

Reputation: 3

In your select statement you should be specific about which trafficCrashReportID you want, the one from InvolvedVehicle or the one from TrafficCrashReport.

Select InvolvedVehicle.trafficCrashReportId

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270773

The problem is that the column is in two tables, and the database doesn't know which to choose. The best solution is to use table aliases and always qualify your column names:

select iv.[trafficCrashReportId], iv.[involvedVehicleId],
       tcr.[assocVehNum], tcr[wasStrikingVehicle]
from InvolvedVehicle iv INNER JOIN
     TrafficCrashReport tcr
     ON iv.trafficCrashReportId = tcr.trafficCrashReportId
Where tcr.addDate between '$Yesterday' AND '$TodayDate';" `

I am guessing which columns come from which tables.

Upvotes: 2

Related Questions