大家帮忙看下，问题出在哪里？

Question

这是我的sql语句
 MySqlCommand oComm = null;
            MySqlDataReader dr = null;
            String sSQL;
            DBMember oMember = new DBMember();
            ArrayList alSetting = new ArrayList(',');
            try
            {
                OpenConnection();
                sSQL = "SELECT * FROM cm_member A LEFT JOIN cm_hotel B WHERE A.mem_hotel=B.htl_sid AND sys_deleted = 'N'";
                if (!String.IsNullOrEmpty(sSearchterm))
                {
                    sSQL += " AND (mem_firstname, ' ', mem_lastname, ' ') LIKE ?1 ";
                }
                if (!String.IsNullOrEmpty(sSearchcountry))
                {
                    sSQL += " AND mem_country LIKE ?2 ";
                }
                if (!String.IsNullOrEmpty(sSearchhotel))
                {
                    sSQL += " AND htl_name LIKE ?3 ";
                }
                sSQL += "ORDER BY sys_modifiedon DESC ";
                oComm = new MySqlCommand(sSQL, oConn);
                if (!String.IsNullOrEmpty(sSearchterm))
                {
                    oComm.Parameters.Add("?1", "%" + sSearchterm + "%");
                }
                if (!String.IsNullOrEmpty(sSearchcountry))
                {
                    oComm.Parameters.Add("?2", "%" + sSearchcountry + "%");
                }
                if (!String.IsNullOrEmpty(sSearchhotel))
                {
                    oComm.Parameters.Add("?3", "%" + sSearchhotel + "%");
                }
                dr = oComm.ExecuteReader();
                Populate(oMember, dr, ref alSetting);            }
            catch (Exception e)
            {
                throw e;
            }报的错误是：#23000Column 'sys_deleted' in where clause is ambiguous看不懂什么意思啊，大家帮我解决下，非常感激！

Answer 1

你关联的两个表中都有sys_deleted这个字段，写SQL时没有指明是哪个表的sys_deleted

Answer 2

  sSQL = "SELECT * FROM cm_member A LEFT JOIN cm_hotel B WHERE A.mem_hotel=B.htl_sid AND sys_deleted = 'N'"; 
是A.sys_deleted 还是B.sys_deleted 

Answer 3

两个表中有相同字段名的话，用SELECT * 也不行，得指出表名

Answer 4

select A.sSearchterm as ass,B.sSearchterm as cccc

Answer 5

把所有地方都改了。where里面也要改。

Answer 6

我把代码全贴出来，你说哪里要改： public override ArrayList ListGallerySearch(String sSearchterm, String sGalleyCaption, String sStatus, int iPage)
        {
            MySqlCommand oComm= null;
            MySqlDataReader dr = null;
            String sSQL;
            DBGallery oGallery = new DBGallery();
            ArrayList alGallery = new ArrayList(',');
            try
            {
                OpenConnection();
                sSQL = "SELECT * FROM (cm_gallery A LEFT JOIN cm_hotel B ON A.gly_hotel=B.htl_sid) WHERE A.sys_deleted='N'";
                if (!String.IsNullOrEmpty(sSearchterm))
                {
                    sSQL += " AND htl_name LIKE ?1 ";
                }
                if (!String.IsNullOrEmpty(sGalleyCaption))
                {
                    sSQL += " AND gly_caption LIKE ?2 ";
                }
                if (!String.IsNullOrEmpty(sStatus))
                {
                    sSQL += " AND gly_status=?3 ";
                }
                //if (!sHotellist.Equals("All"))
                //{
                //    sSQL += " AND (vid_channel=?4 ";
                //    for (int i = 0; i < arHotellist.Length; i++)
                //    {
                //        sSQL += "OR vid_channel=?" + (i + 10).ToString() + " ";
                //    }
                //    sSQL += ")";
                //}
                sSQL += "ORDER BY gly_caption DESC ";
                oComm = new MySqlCommand(sSQL, oConn);
                if (!String.IsNullOrEmpty(sSearchterm))
                {
                    oComm.Parameters.Add("?1", "%" + sSearchterm + "%");
                }
                if (!String.IsNullOrEmpty(sGalleyCaption))
                {
                    oComm.Parameters.Add("?2", "%" + sGalleyCaption + "%");
                }
                if (!String.IsNullOrEmpty(sStatus))
                {
                    oComm.Parameters.Add("?3", sStatus);
                }
                //if (!sHotellist.Equals("All"))
                //{
                //    oComm.Parameters.Add("?4", sHotellist);
                //    for (int i = 0; i < arHotellist.Length; i++)
                //    {
                //        oComm.Parameters.Add("?" + (i + 10).ToString(), arHotellist[i]);
                //    }
                //}
                dr = oComm.ExecuteReader();
                Populate(oGallery, dr, ref alGallery);            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (dr != null) { dr.Close(); }
                if (oComm != null) { oComm.Dispose(); }
                CloseConnection();
            }
            return alGallery;
        }

Answer 7

不好意思，贴错了，这才是代码 public override ArrayList ListMemberSearch(String sSearchterm, String sSearchcountry, String sSearchhotel)
        {
            MySqlCommand oComm = null;
            MySqlDataReader dr = null;
            String sSQL;
            DBMember oMember = new DBMember();
            ArrayList alSetting = new ArrayList(',');
            try
            {
                OpenConnection();
                sSQL = "SELECT * FROM (cm_member A LEFT JOIN cm_hotel B ON A.mem_hotel=B.htl_sid) WHERE A.sys_deleted = 'N'";
                if (!String.IsNullOrEmpty(sSearchterm))
                {
                    sSQL += " AND CONCAT(mem_firstname, ' ', mem_lastname, ' ', mem_email) LIKE ?1 ";
                }
                if (!String.IsNullOrEmpty(sSearchcountry))
                {
                    sSQL += " AND mem_country LIKE ?2 ";
                }
                if (!String.IsNullOrEmpty(sSearchhotel))
                {
                    sSQL += " AND htl_name LIKE ?3 ";
                }
                sSQL += "ORDER BY sys_modifiedon DESC ";
                oComm = new MySqlCommand(sSQL, oConn);
                if (!String.IsNullOrEmpty(sSearchterm))
                {
                    oComm.Parameters.Add("?1", "%" + sSearchterm + "%");
                }
                if (!String.IsNullOrEmpty(sSearchcountry))
                {
                    oComm.Parameters.Add("?2", "%" + sSearchcountry + "%");
                }
                if (!String.IsNullOrEmpty(sSearchhotel))
                {
                    oComm.Parameters.Add("?3", "%" + sSearchhotel + "%");
                }
                dr = oComm.ExecuteReader();
                Populate(oMember, dr, ref alSetting);            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (dr != null) { dr.Close(); }
                if (oComm != null) { oComm.Dispose(); }
                CloseConnection();
            }
            return alSetting;
        }