CODEWITHSUNDEEP
mysqlsql
kinx
kinx

Reputation: 493

How to fix "ambiguous" where clause in procedure

I am getting an error:

1052 - Column 'orderId' in where clause is ambiguous

when trying to do CALL GetOrderById(2000)

BEGIN
    SELECT
        hsorders.*,
        hslineitem.partNum, hslineitem.price
    FROM
        hsorders
    JOIN hslineitem ON hslineitem.orderId = hsorders.orderId
    WHERE
        orderId = orderId;
END

I need to pass an Id and join data from two tables that have the same `orderId``.

What am I doing wrong?

Upvotes: 3

Views: 399

Answers (4)

Lukasz Szozda
Lukasz Szozda

Reputation: 175954

You need to use alias:

BEGIN
    SELECT
        hsorders.*,
        hslineitem.partNum, hslineitem.price
    FROM  hsorders
    JOIN hslineitem ON hslineitem.orderId = hsorders.orderId
    WHERE
        hsorders.orderId = p_orderId;  -- I suggest to rename parameter to avoid 
                                       -- name collision
END

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You are getting the error because orderid is in both tables. Hence, the SQL engine does not know which orderid the where clause refers to.

Let me hypothesize that you have a parameter or variable called orderid.

That name conflicts with columns of the same name in the table. That is why I use prefixes. For instance, if orderid is being passed in as a parameter, I would name it in_orderid. If it were a local variable, I might use v_orderid.

Then the code would look like this:

BEGIN
    SELECT o.*, li.partNum, li.price
    FROM hsorders o JOIN
         hslineitem li
         ON li.orderId = o.orderId
    WHERE o.orderId = v_orderId;
END;

Notice that I also added table aliases so the query is easier to write and to read.

Upvotes: 2

Sas
Sas

Reputation: 278

Use alias while joining tables. It will be helpful to distinguish the columns if they are in both the tables.

When to use alias

BEGIN
SELECT
    HO.*,
    HL.partNum, HL.price
FROM
    hsorders HO
JOIN hslineitem HL 
ON HO.orderId = HL.orderId
END

you have already used ON clause to give HO.orderId = HL.orderId so you do not have to use where clause again

Upvotes: 1

Devsi Odedra
Devsi Odedra

Reputation: 5322

ambiguous means use table name or alias name before FIELD

Like hsorders.orderId in where clause.

Upvotes: 1

Related Questions