Need aliases Internal join column in MySQL

Question

SELECT AlertTypeID FROM incidentalert INNER JOIN incidentalerttype ON incidentalert.alerttypeid=incidentalerttype.AlertTypeID WHERE IncidentID=111210 

AlertTypeID is the column in table 1 and the primary key in table 2. How can I fix the problem?

Answer 1

Try the following:

 SELECT ia.AlertTypeID FROM incidentalert ia inner join incidentalerttype iat on iat.alerttypeid=ia.AlertTypeID where ia.IncidentID=111210 

Answer 2

Just fully qualify the column reference by adding the table name in front. As a best practice, you should do the same for the column in your WHERE . Using aliases for table names can make this a little easier to read.

 SELECT ia.AlertTypeID FROM incidentalert ia INNER JOIN incidentalerttype iat ON ia.AlertTypeID = iat.AlertTypeID WHERE ia.IncidentID = 111210 

Answer 3

Your request should be reorganized, as well as smooth tables

Here is your original request:

 SELECT AlertTypeID FROM incidentalert INNER JOIN incidentalerttype ON incidentalert.alerttypeid=incidentalerttype.AlertTypeID WHERE IncidentID=111210; 

Since you are going to use only one incident identifier trying to extract it before the JOIN happens:

 SELECT ia.AlertTypeID FROM (SELECT AlertTypeID FROM incidentalert WHERE IncidentID=111210) ia INNER JOIN (SELECT AlertTypeID FROM incidentalerttype) iat USING (AlertTypeID) ; 

This should happen much faster as the number of incidents grows.

IF you need all the columns from a row in a random row, change it as follows:

 SELECT ia.* FROM (SELECT * FROM incidentalert WHERE IncidentID=111210) ia INNER JOIN (SELECT AlertTypeID FROM incidentalerttype) iat USING (AlertTypeID) ; 

Give it a try !!!

Answer 4

Just put the table name in front of the column, as in the JOIN statement.