#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2006
    Posts
    4
    Rep Power
    0

    Column in WHERE clause is ambiguous


    I am getting content from my sql database with the following code which works fine:

    Code:
    SELECT *
    FROM tbl_listings
    WHERE postcode = 'var1' AND catID = 'var2' AND stateID = 'var3'
    ORDER BY listingName ASC
    using the following variables

    name: var1
    default value: -1
    run-time value: $_GET['postcode']

    name: var2
    default value: -1
    run-time value: $_GET['catID']

    name: var3
    default value: -1
    run-time value: $_GET['stateID']


    I, however, want to get the category name from the category table using a join so I tried this:

    Code:
    SELECT *
    FROM tbl_listings LEFT JOIN tbl_category ON tbl_listings.catID = tbl_category.catID
    WHERE postcode = 'var1' AND catID = 'var2' AND stateID = 'var3'
    ORDER BY listingName ASC
    using the following variables

    name: var1
    default value: -1
    run-time value: $_GET['postcode']

    name: var2
    default value: -1
    run-time value: $_GET['catID']

    name: var3
    default value: -1
    run-time value: $_GET['stateID']



    This gives me an error saying: Column: 'catID' in where clause in ambiguous.

    Any ideas as to how I should be writing this code in order to filter it by the set variables and still join the category table in order to get the category name? Thanks.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2005
    Posts
    447
    Rep Power
    18
    Code:
    SELECT *
    FROM tbl_listings LEFT JOIN tbl_category ON tbl_listings.catID = tbl_category.catID
    WHERE postcode = 'var1' AND tbl_category.catID = 'var2' AND stateID = 'var3'
    ORDER BY listingName ASC
    either put tbl_category or tbl_listings in front of that catID in the where clause, that's what the error is saying, it doesn't know which catID to use

IMN logo majestic logo threadwatch logo seochat tools logo