有几个表,如下:
帐号表-accountInfo(accountId,month0_Amount,createdId,WDCode)
单位表-companyInfo(accountName,address,createdId)
行业类型大类表-industryBigClass(industryBigId,industryBigName)
行业类型小类表-industrySmallClass(industrySmallId,industrySmallName,industryBigId)
网点表-wangdian(WDID,WDCode,WDName,ZHID)
支行表-zhihang(ZHID,ZHName)要查询属性:accountId,month0_Amount,accountName,address,industrySmallId,industryBigId,WDCodeselect accountId,month0_Amount,accountName,address,c.industrySmallId,isc.industryBigId,a.WDCode
from accountInfo a,companyInfo c,wangdian w,zhihang z,industryBigClass ibc,industrySmallClass isc
where a.createdId = c.createdId and c.WDCode = w.WDCode and w.ZHID = z.ZHID
and c.industrySmallId = isc.industrySmallId and isc.industryBigId = ibc.industryBigId
and w.ZHID =1 and accountId = '44001380041052500016'
传递的参数ZHID和accountId都是存在的,查询无结果。请高手帮忙,谢谢!在线等
帐号表-accountInfo(accountId,month0_Amount,createdId,WDCode)
单位表-companyInfo(accountName,address,createdId)
行业类型大类表-industryBigClass(industryBigId,industryBigName)
行业类型小类表-industrySmallClass(industrySmallId,industrySmallName,industryBigId)
网点表-wangdian(WDID,WDCode,WDName,ZHID)
支行表-zhihang(ZHID,ZHName)要查询属性:accountId,month0_Amount,accountName,address,industrySmallId,industryBigId,WDCodeselect accountId,month0_Amount,accountName,address,c.industrySmallId,isc.industryBigId,a.WDCode
from accountInfo a,companyInfo c,wangdian w,zhihang z,industryBigClass ibc,industrySmallClass isc
where a.createdId = c.createdId and c.WDCode = w.WDCode and w.ZHID = z.ZHID
and c.industrySmallId = isc.industrySmallId and isc.industryBigId = ibc.industryBigId
and w.ZHID =1 and accountId = '44001380041052500016'
传递的参数ZHID和accountId都是存在的,查询无结果。请高手帮忙,谢谢!在线等
a.createdId = c.createdId and c.WDCode = w.WDCode and w.ZHID = z.ZHID
and c.industrySmallId = isc.industrySmallId and isc.industryBigId = ibc.industryBigId
的数据才会被保留下来,
如果ZHID和accountId都是存在的,那么肯定是你要查询的数据不满足上面这一串条件了。
你要查一下具体的数据,在哪一个条件上不满足,导致连空了。ANSI86的这种写法不好纠错和调试,建议用ANSI92的标准:用left outer join ,right outer join ,inner join。
当这个industrySmallId是空的时候我也要能查询其他数据(industrySmallId显示为空),当industrySmallId有数据时就显示全部信息,怎么写?
accountId
,month0_Amount
,accountName
,address
,c.industrySmallId
,isc.industryBigId
,a.WDCode
FROM
accountInfo a
INNER JOIN companyInfo c
ON a.createdId = c.createdId
INNER JOIN wangdian w
ON c.WDCode = w.WDCode
INNER JOIN zhihang z
ON w.ZHID = z.ZHID
LEFT OUTER JOIN industryBigClass ibc --这里改成左连接
ON c.industrySmallId = isc.industrySmallId
INNER JOIN industrySmallClass isc
ON isc.industryBigId = ibc.industryBigId
WHERE
w.ZHID = 1
AND accountId = '44001380041052500016'
accountId
,month0_Amount
,accountName
,address
,c.industrySmallId
,isc.industryBigId
,a.WDCode
FROM
accountInfo a
INNER JOIN companyInfo c
ON a.createdId = c.createdId
INNER JOIN wangdian w
ON c.WDCode = w.WDCode
INNER JOIN zhihang z
ON w.ZHID = z.ZHID
LEFT OUTER JOIN industrySmallClass isc --这里改成左连接
ON c.industrySmallId = isc.industrySmallId
INNER JOIN industryBigClass ibc
ON isc.industryBigId = ibc.industryBigId
WHERE
w.ZHID = 1
AND accountId = '44001380041052500016'