如何在SQL中的select查询中根据条件从另一个表中获取数据？ [英] How to fetch data from another table based on condition with in a select query in SQL?

问题描述

我想从一个表（比如AutoReturnPostingDetails）或另一个表（比如Paramconfig）中获取数据，这是基于sql中主选择查询中的状态。



我的查询如下：

I want to fetch data from a table (say AutoReturnPostingDetails) or from another table (say Paramconfig) based on status with in a main select query in sql.

My query is as below:

select  id,                           
CASE WHEN ITCFinal.AutoReturnStatus=7004          
      THEN (select DebitAccNo from AutoReturnPostingDetails AP         
      where AP.OutwardInstFinalId=ITCFinal.OutwardInstFinalId)        
      ELSE @DebitAccount END AS DebitAccount       
                                         
                                          
 FROM OUT_ITC_Final ITCFinal 

我尝试过：

What I have tried:

Select  id,                           
CASE WHEN ITCFinal.AutoReturnStatus=7004          
      THEN (select DebitAccNo from AutoReturnPostingDetails AP         
      where AP.OutwardInstFinalId=ITCFinal.OutwardInstFinalId)        
      ELSE @DebitAccount END AS DebitAccount       
                                         
                                          
 FROM OUT_ITC_Final ITCFinal

推荐答案

您将要使用连接。基本上，如果我理解你正确地尝试SQL，你想要使用匹配记录中的DebitAccNo（如果它存在），如果它不是你想要使用传入的参数。使用LEFT JOIN。

You'll want to use a join instead. Essentially, if I understand your attempt at SQL correctly, you want to use the DebitAccNo from a matching record if it exists and if it does not you want to use the passed in parameter. Use LEFT JOIN for this.

SELECT id, COALESCE(t2.DebitAccNo, @DebitAccount) 
FROM OUT_ITC_Final t1 
LEFT JOIN AP.OutwardInstFinalID t2 ON t1.OutwardInstFinalID = t2.OutwardInstFinalID
WHERE t1.AutoReturnStatus = 7004

这些内容。

Something along these lines.

这篇关于如何在SQL中的select查询中根据条件从另一个表中获取数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！