Fixing Ambiguous Column in WHERE Clause

Question

Fixing Ambiguous Column in WHERE Clause

912 views Asked by Rataiczak24 At 16 December 2016 at 16:15

I need to display a table that corresponds to the value chosen after I select a value in a dropdown list. If I run this query in my SQLPro Studio, and obviously without the $mr_id variable, it says that MR_ID is ambiguous. Is there a way to fix this? This seems like a small problem to me but I can't seem to figure it out. I just need to make sure there is a WHERE clause somewhere so that it will only display the values that correlate to the value selected.

The table is only 2 columns, MR_ID (which is what is displayed in the dropdown list and also concatenated with another column not in the table) and Supp_ID.

<?php
$host="xxxxxxxxxxxx"; 
$dbName="xxxxx"; 
$dbUser="xxxxxxxx"; 
$dbPass="xxxxxxxxxxxxxx";

$mr_id = $_POST['mr_id'];

$dbh = new PDO( "sqlsrv:server=".$host."; Database=".$dbName, $dbUser, $dbPass);
$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

$sql_one = "
SELECT CONCAT(CAST(Stage_Rebate_Index.MR_ID AS INT),' - ', Stage_Rebate_Master.MR_Name) AS MR_ID,
       Stage_Rebate_Index.MR_ID AS sort_column, 
       CAST(Supp_ID as INT) AS Supp_ID
FROM   Stage_Rebate_Index
  LEFT JOIN Stage_Rebate_Master 
    ON Stage_Rebate_Master.MR_ID = Stage_Rebate_Index.MR_ID
WHERE  Stage_Rebate_Index.MR_ID = '$mr_id'
ORDER BY sort_column";


//$users = $dbh->query($sql);
$users_one = $dbh->query($sql_one);
?>

<html>
    <body>

        <!-- Table -->
<p> 
    <div id="table_div">
        <table border="1" id="index_table" class="ui-widget ui-widget-content">
            <thead>
                <tr class="ui-widget-header">
                <td>MR ID</td>
                <td>Supplier ID</td>
                </tr>
            </thead>
            <?php foreach($users_one->fetchAll() as $supp) { ?>
            <tr>
                <td class="mr_id"><?php echo $supp['MR_ID'];?></td>
                <td class="supp_id"><?php echo $supp['Supp_ID'];?></td>
            </tr>
            <?php } ?>
        </table>
    </div>

    </body>
    </html>

EDIT: Updated query with harcoded value...

$sql_one = "
SELECT 
       CONCAT(CAST(t1.MR_ID AS INT),' - ', COALESCE(t2.MR_Name, '')) AS MR_ID,
       t1.MR_ID AS sort_column, 
       CAST(Supp_ID as INT) AS Supp_ID
FROM Stage_Rebate_Index t1
      LEFT JOIN Stage_Rebate_Master t2
         ON t2.MR_ID = t1.MR_ID
WHERE  
  CONCAT(CAST(t1.MR_ID AS INT),' - ', t2.MR_Name) = LTRIM(RTRIM('1 - Company A'))
ORDER BY sort_column";

Original Q&A

There are 3 answers

**Zackary Murphy** · Answer 1 · 2016-12-16 16:17:50

Zackary Murphy On 16 December 2016 at 16:17

In your where clause

WHERE
    MR_ID = '$mr_id'

Specify the table you want it from as such:

WHERE
    Stage_Rebate_Master.MR_ID = '$mr_id'

You should probably also specify the tables in your select clause too.

**nurdyguy** · Answer 2 · 2016-12-16 16:39:08

From your comment on Zackary Murphy's solution, it sounds like your MR_ID you want in the where is actually the computed value in the select, not the column value on the tables. Try this.

SELECT 
       CONCAT(CAST(t1.MR_ID AS INT),' - ', COALESCE(t2.MR_Name, '')) AS MR_ID,
       t1.MR_ID AS sort_column, 
       CAST(Supp_ID as INT) AS Supp_ID
FROM Stage_Rebate_Index t1
      LEFT JOIN Stage_Rebate_Master t2
         ON t2.MR_ID = t1.MR_ID
WHERE  
  CONCAT(CAST(t1.MR_ID AS INT),' - ', t2.MR_Name) = LTRIM(RTRIM('$mr_id'))
ORDER BY sort_column

*Edit to add table aliases.

*Edit Added white space trim.

*Edit added COALESCE in case t2.MR_Name is null.

**Arvind Maurya** · Answer 3 · 2016-12-17 06:37:53

You have to give fully qualified name of table columns.

Like you have 2 tables, table1 and table2, and have col1 as column name in both table

If you are retrieving data from these 2 table below is query example

select 
    table1.co11 as tbl1col1, table2.col1 as tbl2col1 
from 
    table1 
join 
    table2 on table1.id = table2.id 
where 
    table1.col1 = "your statement" and table2.col1 = "your statement"

TechQA.

Fixing Ambiguous Column in WHERE Clause

There are 3 answers

Related Questions in JAVASCRIPT

Related Questions in PHP

Related Questions in SQL-SERVER

Related Questions in AJAX

Related Questions in AMBIGUOUS

Popular Questions

Popular Tags

Trending Questions