none
search for a field of a given value in each of many tables RRS feed

  • Question

  • maybe wrong forum but......


     

    cmd.CommandText =
    " SELECT " + sFieldName +
    "FROM AIs,AOs,DIs,DOs,MIs,FIDs,BAIs,BAOs,BAVs,BBIs,BBOs,BBVs,BDEs,SYs" +
    " WHERE VEC = '" + sVECName + "'" +
    " AND name = '" + sPtName + "';";

    The same key fields "VEC" and "name" occur in each of the tables listed.  An entry is to be found where these two values match the supplied values for "VEC" and "name" respectively.  There should only be one such match in all the tables.

    The requested field sFieldName may or may not exist in any given table.
    The above does not work.
    It responds indicating the table is ambiguous.

    How should this select be written?

    /Boyd

    Wednesday, July 22, 2009 3:56 PM

Answers

  • If names are the same in multiple tables you need to use alias or full table name syntax to refer specific table. Select statement cannot assume that you want to select from all the table that have same field name. In addition "name" is a reserved word in many databases and not recommended as name of any objects inside of database (columns, tables or anything else). You will need to restructure your query to use UNION or put OR condition that matches each table column, like

    SELECT ..... FROM AIs,AOs,DIs,DOs,MIs,FIDs,BAIs,BAOs,BAVs,BBIs,BBOs,BBVs,BDEs,SYs WHERE (AIs.VEC = 'value here' AND AIs.[name] = 'value here') OR (AOs.VEC = 'value here' AND AOs.[name] = 'value here') OR ......
    Val Mazur (MVP) http://www.xporttools.net
    Thursday, July 23, 2009 10:34 AM
    Moderator
  • This is because you specify vec field in your SELECT list and did not specify from which table and this is what exception message points to. Since field with the same name exists in multiple tables, database engine has no idea which one to pickup. Your final statement should look like 


    SELECT AIs.vec FROM AIs,SYs WHERE ......
    Val Mazur (MVP) http://www.xporttools.net
    Friday, July 24, 2009 10:04 AM
    Moderator

All replies

  • hello mabe you can use 'union'
    

    Wenn ich dich hab’,gibt es nichts, was unerträglich ist.坚持不懈!http://hi.baidu.com/1987raymond
    Thursday, July 23, 2009 2:15 AM
  • If names are the same in multiple tables you need to use alias or full table name syntax to refer specific table. Select statement cannot assume that you want to select from all the table that have same field name. In addition "name" is a reserved word in many databases and not recommended as name of any objects inside of database (columns, tables or anything else). You will need to restructure your query to use UNION or put OR condition that matches each table column, like

    SELECT ..... FROM AIs,AOs,DIs,DOs,MIs,FIDs,BAIs,BAOs,BAVs,BBIs,BBOs,BBVs,BDEs,SYs WHERE (AIs.VEC = 'value here' AND AIs.[name] = 'value here') OR (AOs.VEC = 'value here' AND AOs.[name] = 'value here') OR ......
    Val Mazur (MVP) http://www.xporttools.net
    Thursday, July 23, 2009 10:34 AM
    Moderator
  • I simplified it to:

    cmd.CommandText = " SELECT " + sFieldName +
      " FROM AIs,SYs" +
      " WHERE ( AIs.VEC = '" + sVECName + "' AND AIs.name  = '" + sPtName + "') OR" +
            " ( SYs.VEC = '" + sVECName + "' AND SYs.name  = '" + sPtName + "'); ";
    

    A debug output displays this as a final sql command:



    <?xml version="1.0" encoding="utf-8" ?> 
      <string xmlns="http://localhost/VCIWebServiceWrite">SELECT vec FROM AIs,SYs WHERE ( AIs.VEC = 'LABRAT' AND AIs.name = 'labrat') OR ( SYs.VEC = 'LABRAT' AND SYs.name = 'labrat');</string> 


    As it is I get:


    .......System.Data.SqlClient.SqlException: Ambiguous column name 'vec'. at System.Data.SqlClient.SqlConnection.OnError(.....
    
    
    wher 'vec' is the value for sFieldName

    If I further simplify it to look in only one table at a time it works, given the identical sFieldName.




    what next?




    Thursday, July 23, 2009 2:05 PM
  • This is because you specify vec field in your SELECT list and did not specify from which table and this is what exception message points to. Since field with the same name exists in multiple tables, database engine has no idea which one to pickup. Your final statement should look like 


    SELECT AIs.vec FROM AIs,SYs WHERE ......
    Val Mazur (MVP) http://www.xporttools.net
    Friday, July 24, 2009 10:04 AM
    Moderator
  • Val,

    Thanks for the response.

    Yes that is true.  That is why the massive OR structure.  The intent is to look in the first table, if not found look in the next table, continue until the WHERE is satisfied or until all out of tables.

     

     

    As a "quickfix" the code has been changed to:

     

    ...
    string[] stringArray = { "SYs", "AIs","AOs","AVS", "DIs","DOs","DVS", "FIDs","MIs", "ACs","HPs","VAVs", "BAIs","BAOs","BAVS", "BBIs","BBOs","BBVS", "BDEs","BVAs","BACs","BHPs","BDDs", "LCs","SCHs","HOLs" }; foreach (string myString in stringArray) { cmd.CommandText = " SELECT " + sFieldName + " FROM " + myString + " WHERE " + myString + ".VEC = '" + sVECName + "' AND " + myString + ".name = '" + sPtName + "';"; //return (cmd.CommandText); cmd.CommandType = CommandType.Text; cmd.Connection = sqlcon; try { String sFieldValue = ""; sqlcon.Open(); int iCount = 0; using (SqlDataReader rdr = cmd.ExecuteReader()) { while ((iCount < 1) && rdr.Read()) { sFieldValue = rdr[0].ToString(); iCount++; } } cmd.Connection.Close(); if (iCount !=0) return (sFieldValue); } ...



    so that it rolls through each table one at a time.

    It would be much more efficient though to do this in a single sql query.

    /Boyd

    Friday, July 24, 2009 1:55 PM
  • it is fine using ORs, but you need to use aliases if you have same column names in multiple tables and try to use them in your query, otherwise query engine has no idea which one to use.
    Val Mazur (MVP) http://www.xporttools.net
    Monday, July 27, 2009 10:28 AM
    Moderator
  • As per your example above, that would only work if the answer is in table AIs.

    Your example from before that yielded the above error message.

    There should be only one match at most in the 26 tables.

    /Boyd

    Monday, July 27, 2009 4:06 PM
  • I do not know the logic of your queries and I did not analyze it, all I have done is pointed to the problem you experieneced with the error. If you know that qury returns incorrect number of rows, then you need to analyze it and see why logic in that query is wrong.
    Val Mazur (MVP) http://www.xporttools.net
    Tuesday, July 28, 2009 10:34 AM
    Moderator