#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Mar 2010
    Posts
    2
    Rep Power
    0

    SQL Error - Joins - column specified multiple times


    Hello,

    I'm getting the following SQL error

    Microsoft OLE DB Provider for SQL Server error '80040e14'

    The column 'category' was specified multiple times for 'J'.

    The query causing the error is:

    Code:
    getListing.Source = "SELECT L.listingID, I.imagePath, L.title, L.categoryID AS categoryID, C.category AS category, S.category AS subCategory FROM ClassImage I RIGHT OUTER JOIN (SELECT L.listingID, L.title, L.categoryID, C.category, S.category FROM ClassListing L, ClassCategory S, ClassCategory C WHERE S.parentID = C.categoryID AND L.categoryID = S.categoryID AND L.invoiceID = " & invoiceID & ") AS J ON J.listingID = I.listingID;"
    I've been trying to figure out the problem but have had no luck!

    Any help solving the problem would be greatly appreciated,

    Thanks,
  2. #2
  3. Contributing User
    ASP High Scholar (3500 - 3999 posts)

    Join Date
    Mar 2006
    Location
    South Wales
    Posts
    3,674
    Rep Power
    2047
    Hi,

    Welcome to the forums. Its hard to help without understanding your table structures and seeing a sample of your data, but my first thought is that you are selecting the majority of your fields from within a sub-select and giving this teh alias of J. Therefore, when you refer to the fields they would no longer have the original table alias, they would have the alias of J. Also, you have two fields called category within the sub-select so you need to give them different names. eg:
    Code:
    getListing.Source = "SELECT J.listingID, I.imagePath, J.title, " & _
    "J.categoryID AS categoryID, " & _
    "J.category1 as category, J.category2 AS subCategory " & _
    "FROM ClassImage I RIGHT OUTER JOIN " & _
    "(SELECT L.listingID, L.title, L.categoryID, C.category as category1, S.category as category2 FROM ClassListing L, ClassCategory S, " & _
    "ClassCategory C WHERE S.parentID = C.categoryID AND L.categoryID = S.categoryID AND L.invoiceID = " & invoiceID & ") " & _
    "AS J ON J.listingID = I.listingID;"
    I hope this makes sense, if you still have problems please post your table structure and a sample of your data and we will try and assist you.
    Last edited by sync_or_swim; March 13th, 2010 at 04:24 AM.
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Mar 2010
    Posts
    2
    Rep Power
    0
    It worked!

    Thank you so much!

Similar Threads

  1. Trying to figure out best way
    By shem in forum ASP Development
    Replies: 10
    Last Post: February 8th, 2007, 04:43 AM
  2. SQL update problem
    By smallboy in forum ASP Development
    Replies: 8
    Last Post: November 11th, 2005, 04:32 AM
  3. query AND/OR for textboxes
    By gilgalbiblewhee in forum ASP Development
    Replies: 1
    Last Post: February 4th, 2005, 06:39 PM
  4. Cannot establish a connection to SQL Server 2K sp3a remotely
    By ngandy in forum Microsoft SQL Server
    Replies: 6
    Last Post: January 19th, 2005, 02:54 AM
  5. search help
    By dev5 in forum ASP Development
    Replies: 18
    Last Post: March 5th, 2004, 10:07 AM

IMN logo majestic logo threadwatch logo seochat tools logo