使用join而不是以下查询 [英] using join instead of the following query

问题描述

我需要在相同条件下使用join而不是此查询

I need to use join instead of this query with same condition

select 
((select (case when sum(A.[Quantity]) is null then 0 else sum(A.[Quantity]) END) 
from [tablename1] A where ((A.[Status]=4) AND (A.[Spcl Order]=1) 
AND (A.[Finished Date] = aa.FromDate ))) 
+ 
(select (case when sum(A.[Quantity]) is null then 0 else sum(A.[Quantity]) END) 
from [tablename2] A where ((A.[Item Category Code] = 'STYLES') AND (A.[Prod_ Order No_]='')
 AND ( A.[Buy-from Vendor No_] in (@vendor)) AND (A.[Quantity]>0) AND (A.[Spcl Order]= 1)
 AND ( A.[Posting Date] = aa.FromDate))))

推荐答案

我不确定我是否正确理解数据，但根据我收集的内容，如果确实存在两个表中的连接关系，则可以轻松使用以下查询。我也猜到了2张桌子上有PK和FK，因此有加入的关系。



一件事......这个值是多少？

I am not sure if I understand the data correctly but from what I gather you can easily use the below query if there is indeed a relationship to join on in both tables. I also guessed that the 2 tables have in place PKs and FKs and as such have relationship to join on.

One thing... What is this value?

aa.FromDate

select (case when sum(A.[Quantity]) is null then 0 else sum(A.[Quantity]) END) AS AQuantity,
	   (case when sum(B.[Quantity]) is null then 0 else sum(B.[Quantity]) END) AS BQuantity
from [tablename1] A
join [tablename2] B
	on -- whatever is the same in the 2 tables
	where A.[Status] = 4
	AND A.[Spcl Order]=1
	AND A.[Finished Date] = @DateValue
	AND B.[Item Category Code] = 'STYLES'
	AND B.[Prod_ Order No_]=''
	AND B.[Buy-from Vendor No_] in (@vendor)
	AND B.[Quantity] > 0
	AND B.[Spcl Order]= 1
	AND B.[Posting Date] = @DateValue

希望这有助于

Hope this helps

这篇关于使用join而不是以下查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！