Need to alias Inner join column in MySQL

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

Al开发者_JS百科ertTypeID is a column in table 1, and a primary key in table 2. How can I disambiguate?

Try this:

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

Simply 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 clause as well. Using aliases for the table names can make this a bit easier to read too.

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

Your query should be refactored as well as aliasing the tables

Here is your original query:

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

Since you are going for only one IncidentID trying retrieving it before the JOIN takes place:

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

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

IF you need all the columns from the incidentalert 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 !!!

Just place the table name before the column, just like you do in the JOIN statement.