最近有人告诉我要添加评论字段。由于这些注释用于会计处理,因此我创建了一个新表。CITCommentID,CITComment,DealID,CITCurrentComment是表中的字段。我必须更新一条SQL语句,该语句从多个表中选择多个项目以显示给用户。我遇到的问题是,由于该表中还没有数据,所以我无法检索其他信息。这是我的声明。
SELECT vl.dealid,
stocknumber,
dealnumber,
newdeal,
archived,
dealershipname,
salespersonnumber,
customername,
grossprofit,
dealdate,
make,
model,
newused,
amtfinanced,
category,
bankname,
customernumber,
finame,
status,
locationname,
datereceived,
comment,
titled,
changedby,
funded,
contractsreturned,
citcomment DateChanged
FROM tvehiclelog vl
JOIN tcustomer cu
ON cu.customerid = vl.customerid
JOIN tbank b
ON b.bankid = vl.bankid
JOIN tmake m
ON m.makeid = vl.makeid
JOIN tdealerships d
ON d.dealershipid = vl.dealershipid
JOIN tsalesperson sp
ON sp.salespersonid = vl.salespersonid
JOIN tmodel ml
ON ml.modelid = vl.modelid
JOIN tinternallocations il
ON il.internallocationid = vl.internallocationid
JOIN tvehiclecomments vcc
ON vl.commentid = vcc.commentid
JOIN tcontractsintransitcomments citc --This is new join
ON vl.citcommentid = citc.citcommentid --CommentID relates to the table vl
JOIN tfimanagers fi
ON fi.fimanagerid = vl.fimanagerid
WHERE vl.dealid = 5224
GROUP BY vl.dealid,
[stocknumber],
[newdeal],
[archived],
[customername],
[dealdate],
[amtfinanced],
[bounced],
[comment],
[locationname],
[daysout],
[datereceived],
[dealnumber],
[dealershipname],
[salespersonnumber],
[grossprofit],
[make],
[model],
[newused],
[category],
[bankname],
[customernumber],
[finame],
[status],
[locationname],
[datereceived],
[comment],
[titled],
[changedby],
[funded],
[contractsreturned],
[datechanged],
citcomment
ORDER BY [datechanged] ASC;
即使没有与记录相关的注释,我也需要此语句返回结果。由于这是一个没有注释的新表,因此它仍然需要显示其他信息。我仍在学习MSSQL,但还不知道所有的技巧。任何帮助是极大的赞赏!
将其更改为LEFT JOIN.
A左联接意味着即使没有与结果中的记录相关的注释,select语句也将返回结果。
LEFT JOIN tcontractsintransitcomments citc --This is new join
ON vl.citcommentid = citc.citcommentid --CommentID relates to the table vl
这是完整的SQL:
SELECT vl.dealid,
stocknumber,
dealnumber,
newdeal,
archived,
dealershipname,
salespersonnumber,
customername,
grossprofit,
dealdate,
make,
model,
newused,
amtfinanced,
category,
bankname,
customernumber,
finame,
status,
locationname,
datereceived,
comment,
titled,
changedby,
funded,
contractsreturned,
citcomment DateChanged
FROM tvehiclelog vl
JOIN tcustomer cu
ON cu.customerid = vl.customerid
JOIN tbank b
ON b.bankid = vl.bankid
JOIN tmake m
ON m.makeid = vl.makeid
JOIN tdealerships d
ON d.dealershipid = vl.dealershipid
JOIN tsalesperson sp
ON sp.salespersonid = vl.salespersonid
JOIN tmodel ml
ON ml.modelid = vl.modelid
JOIN tinternallocations il
ON il.internallocationid = vl.internallocationid
JOIN tvehiclecomments vcc
ON vl.commentid = vcc.commentid
LEFT JOIN tcontractsintransitcomments citc --This is new join
ON vl.citcommentid = citc.citcommentid --CommentID relates to the table vl
JOIN tfimanagers fi
ON fi.fimanagerid = vl.fimanagerid
WHERE vl.dealid = 5224
GROUP BY vl.dealid,
[stocknumber],
[newdeal],
[archived],
[customername],
[dealdate],
[amtfinanced],
[bounced],
[comment],
[locationname],
[daysout],
[datereceived],
[dealnumber],
[dealershipname],
[salespersonnumber],
[grossprofit],
[make],
[model],
[newused],
[category],
[bankname],
[customernumber],
[finame],
[status],
[locationname],
[datereceived],
[comment],
[titled],
[changedby],
[funded],
[contractsreturned],
[datechanged],
citcomment
ORDER BY [datechanged] ASC;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句