Fixing Ambiguous Column in WHERE Clause

Question

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";

Update the question and remove **everything** that **isn't** related to your question. If you ask about SQL - remove all html/css/javascript from your question. — Dekel, Dec 16 '16 at 16:17
you should start using alias for your table names, that would make this much easier — GuidoG, Dec 16 '16 at 16:27

score 3 · Answer 1 · answered Dec 16 '16 at 16:17

3

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.

answered Dec 16 '16 at 16:17

Zackary Murphy

421
5
10

This is good in that it gets rid of my ambiguous column, but this concatenated value is not in the Stage_Rebate_Index or Master table...its just a join between the 2 – Rataiczak24 Dec 16 '16 at 16:25
Change the alias in the select statement to not match the column name, then use it in the where. That or use the entire concatenation formula in the where clause. – nurdyguy Dec 16 '16 at 16:37

nurdyguy · Answer 2 · 2016-12-16T19:56:30.573

2

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.

edited Dec 16 '16 at 19:56

answered Dec 16 '16 at 16:39

nurdyguy

2,876
3
25
32

This works but for some reason, isnt picking up the value of `$mr_id`....i even did an `echo $mr_id` to make sure the value is being brought in properly and it is. – Rataiczak24 Dec 16 '16 at 16:48
Do an echo on the `$sql_one` itself, I'm curious as to how that builds out. – nurdyguy Dec 16 '16 at 17:08
Okay, found the problem....the format of the values is `1 - Company A`, however, when this query is run, it comes out as ` 1 - Company A `...so there is an extra space before the 1 and after A...what should it look like then? – Rataiczak24 Dec 16 '16 at 17:12
Sounds like we just need to trim. I'll update the query. – nurdyguy Dec 16 '16 at 17:25
Whenever I echo this out and put it into my SQLPro Studio, it works...however, still nothing happens when i select a value in my dropdown...not sure why that wouldnt work – Rataiczak24 Dec 16 '16 at 17:30
Then that sounds more line a db connection issue maybe? Try it with the value hard coded in, see what you get. If nothing, replace the entire query with something super simple that you know would return a value, see if it runs. I also noticed that you are using `Left join`. Is if possible you are getting a null value for `t2.MR_Name`? That might cause some weirdness. – nurdyguy Dec 16 '16 at 19:37
I added a `COALESCE` to deal with nulls. I don't think this is your issue but it is a good practice. – nurdyguy Dec 16 '16 at 19:58
Correct, not the issue, but I did add it to my code just in case. – Rataiczak24 Dec 16 '16 at 20:00
Can you edit the original post and add the query where you hard-coded the value and it worked? – nurdyguy Dec 16 '16 at 20:03
Well, nothing is out of place and I can't debug any further remotely. My hunch is that there is still a white space issue, probably before and/or after the ' - ' and probably in the `$mr_id` value itself. It is impossible for me to know or test, just a guess. All I can say is try to test that kind of stuff. Good luck! – nurdyguy Dec 16 '16 at 20:20
Pretty sure its a datatype problem...it works if the column is a FLOAT type but my column is VARCHAR and doesnt work...any idea with that information? – Rataiczak24 Dec 20 '16 at 15:50
Are you saying that the `t1.MR_ID` is a varchar? – nurdyguy Dec 20 '16 at 22:11
I actually was able to fix my problem...appreciate it though! – Rataiczak24 Dec 21 '16 at 13:41

score 0 · Answer 3 · edited Jan 08 '17 at 08:28

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"

Fixing Ambiguous Column in WHERE Clause

3 Answers3