Ambiguous Column name in Sql expression

Question

I have two tables; they are joined by relationship detail on a column name via Delphi ado link

1st table has bunch of data and a fileref as key 1, 2nd table has rows of data and a fileref as key 2

1st table has other information but one fileref value, 2nd table hold many fileref values but different accounts

Table 1: id, fileref, 1, 2, 3, 4, 5, accno, 7, 8, 9, etc, etc...

table 2: id, fileref, accno

  SELECT * FROM vtindex a
  JOIN vi_accno b
  ON b.fileref = a.FileRef
  WHERE (a.AccNo like '%123456789%') or (b.accno like '%123456789%')

Above is the query where i get the ambiguous error

the idea is that if I dont find the accno is table 1 it must try and find it in table 2

hope this makes sense and whats wierd is that if i run the query inside MSSMS the query returns results without errors

Answer 1

You're going to have to declare your columns, something like this;

SELECT
     a.ID A_ID
    ,a.fileref A_fileref
    ,a.Field1 A_Field1
    ,a.Field2 A_Field2
    ,a.accno A_Accno
    ,b.id B_ID
    ,b.fileref B_fileref
    ,b.accno B_accno
FROM vtindex a
JOIN vi_accno b
    ON a.fileref = b.fileref
WHERE a.AccNo like '%123456789%'
    OR b.accno like '%123456789%'

If you only want to declare some then do this;

SELECT
     a.*
    ,b.id B_ID
    ,b.fileref B_fileref
    ,b.accno B_accno
FROM vtindex a
JOIN vi_accno b
    ON a.fileref = b.fileref
WHERE a.AccNo like '%123456789%'
    OR b.accno like '%123456789%'

Your issue is that you're using fields like id, fileref and accno more than once (from each table) and the names are clashing. If you change the names on your table that only has 3 records then you can get away with leaving table a as-is.

Answer 2

You have the same column name in both tables, so using * pulls both and the names conflict. In SSMS you are allowed to do that as you are only viewing the results on screen but as soon as you send the data to a destination that expects column names to be unique you will get an error. You will have to explicitly name the columns you want in your select list.

Also, based on your question I believe you will want to use coalesce(b.accno, a.accno). This will use the accno from A only if accno is null in B.

EDIT: Based on comments I believe here is the specific syntax I was referring to. Note how coalesce solve the problem of column aliases by having only one field with the name you want.

SELECT ID, FileRef, cnum, Month, Type, Typei, 
       PropDesc, Coalesce(a.accno, b.accno) AccNo, person, client, idno,        
       Consultant, Memo, qclose, vtdate 
    FROM vtindex a
    Join vi_accno b ON b.fileref = a.FileRef
    WHERE (a.AccNo like '%123456789%') or (b.accno like '%123456789%')