CODEWITHSUNDEEP
mysqlinner-join
patrick
patrick

Reputation: 16949

Need to alias Inner join column in MySQL

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

AlertTypeID is a column in table 1, and a primary key in table 2. How can I disambiguate?

Upvotes: 4

Views: 7231

Answers (4)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

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 !!!

Upvotes: 2

everag
everag

Reputation: 7662

Try this:

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

Upvotes: 5

Dan Short
Dan Short

Reputation: 9616

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

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135799

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

Upvotes: 2

Related Questions