从Query中的第二个表中获取值

select * 
from EFLOVRelationship R 
where R.parentEFLOVValueId = '5320' 
  and R.childEFLOVId in (select Eflovid 
                         from EFFieldLOVStaticValue  
                         where efLovId = 49)

In the above query I want to fetch 2 columns from EFFieldLOVStaticValue table and display.

在上面的查询中，我想从EFFieldLOVStaticValue表中获取2列并显示。

Thanks in advance

提前致谢

2 个解决方案

#1

Use Inner Join

使用内部联接

SELECT R.*, 
       V.field1, 
       V.field2 
FROM   eflovrelationship R 
       INNER JOIN effieldlovstaticvalue V 
               ON R.childeflovid = V.eflovid 
WHERE  R.parenteflovvalueid = '5320' 
       AND V.eflovid = 49

If your table has 1:N relationship then use Distinct to avoid duplicates in result

如果您的表具有1：N关系，则使用Distinct以避免重复结果

If you want just one record from effieldlovstaticvalue table for each R.childeflovid column then use Cross Apply but you need to use required column to order the result and choose the top 1 record

如果您只需要每个R.childeflovid列的effieldlovstaticvalue表中的一条记录，那么请使用Cross Apply但您需要使用必需的列来订购结果并选择前1条记录

SELECT R.*, 
       CS.field1, 
       CS.field2 
FROM   eflovrelationship R 
       CROSS apply (SELECT TOP 1 field1, 
                                 field2 
                    FROM   effieldlovstaticvalue V 
                    WHERE  R.childeflovid = V.eflovid 
                           AND eflovid = 49 
                    ORDER  BY someordercolumn) CS 
WHERE  R.parenteflovvalueid = '5320'

#2

Use join:

使用加入：

select R.*, sv.?, sv.?
from EFLOVRelationship R join
     EFFieldLOVStaticValue sv
     ON sv.Eflovid = R.childEFLOVId and sv.efLovId = 49
where R.parentEFLOVValueId = '5320'

#1