SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Column 'ID' in field list is ambiguous

    Hi all.

    My tables in DB MySQL:

    Code:
    doTable_A
    ID	NAME_A
    1	AAA
    2	BBB
    
    
    doTable_B
    ID 	NAME_B
    1	CCC
    2	DDD
    I need this output:

    Code:
    ID 	NAME
    1	AAA
    2	BBB
    3	CCC
    4	DDD
    And this is my query:

    Code:
    SELECT  
        DISTINCT (ID),  
        (NAME_A)  
    FROM  
        doTable_A  
           LEFT OUTER JOIN 
        doTable_B 
           ON B.ID = A.ID
    Response whit error:

    Column 'ID' in field list is ambiguous

    Why?

    kind regards
    viki

  2. #2
    SitePoint Evangelist
    Join Date
    Aug 2007
    Posts
    566
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it's because you have 2 tables with both a field id, so the engine is not sure which field to base it's output on.

    Prefix with a.id, or b.id, depending on which table you want the field be outputed
    Code:
    SELECT  
        DISTINCT (a.ID),  
        (a.NAME_A)  
    FROM  
        doTable_A  as a
           LEFT OUTER JOIN 
        doTable_B as b 
           ON B.ID = A.ID

  3. #3
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,824
    Mentioned
    73 Post(s)
    Tagged
    6 Thread(s)
    You have two tables both with an ID field.

    Check out how the exact SQL syntax for the "AS" command.

    This is from my failing memory


    SELECT
    DISTINCT (fred.ID),
    (NAME_A)
    FROM
    doTable_A as fred
    LEFT OUTER JOIN
    doTable_B as jack
    ON B.ID = A.ID

  4. #4
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for yours replies but with correct query I dont have this output:

    Code:
    ID 	NAME
    1	AAA
    2	BBB
    3	CCC
    4	DDD

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    using a JOIN, you're not going to get that output. try a union instead:
    Code:
    select ID
         , name_A
      from doTable_A
     union all
    select ID + (select max(id) from doTable_A)
         , name_B
      from doTable_B
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  6. #6
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    using a JOIN, you're not going to get that output. try a union instead:
    Code:
    select ID
         , name_A
      from doTable_A
     union all
    select ID + (select max(id) from doTable_A)
         , name_B
      from doTable_B
    Thanks, but I Have this error.

    Microsoft OLE DB Provider for ODBC Drivers error '80004005'

    [mysqld-5.0.45-community-nt-log]The used SELECT statements have a different number of columns

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    all the SELECTs in a UNION query have to return the same number of columns

    maybe you could show us your real query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    all the SELECTs in a UNION query have to return the same number of columns

    maybe you could show us your real query?
    Sorry my mistake... this query it's OK.
    thanks x your help


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •