选择至少具有与合同要求相同的批准代码的供应商 [英] Selecting suppliers that have at least the same approval codes as the contract requires

问题描述

有一个带有批准代码（VNDAPP）的供应商表和一个带有要求代码（COXA）的合同表。合同可以有任意数量的要求，供应商可以有任意数量的批准。

  VNDAPP ：（供应商批准表）
 VNUM（供应商编号）
 REQMT（批准代码）
 
 COXA ：（合同要求表）
 CONTR（合同号）
 REQMT（要求）
  

需要找到所有已获批准至少符合某项合同要求的供应商。

示例合同7736的要求编号为1和10。有27个供应商的最低要求均为这两个。由于需求是可变的，因此我无法将其硬编码到查询中。我不确定这是否可以视为关系划分问题...

  SELECT VNDNO 
从VNDAPP 
 VNDAPP.REQMT上的左联接COXA = COXA.REQMT 
 VNDAPP.VNDNO的组
具有计数（COXA.REQMT）=（选择计数（*）
来自COXA WHERE COXA.CONTR = '7736'
 GROUP BY COXA.CONTR）
  

我在做什么错？ / p>

预先感谢！

示例数据：

 合同要求（COXA）：
合同要求
 7736 1 
 7736 10 
 7737 1 
 7737 4 
 7737 6 
 7738 5 
 7739 1 
 
 
 
供应商认可（VNDAPP）：
 VNDNO REQMT 
 10019 1 
 10020 1 
 10020 2 
 10020 10 
 10021 1 
 10021 4 
 10021 5 
 10021 6 
 
 
 
期望的结果：
 CONTR VNDNO 
 7736 10020 
 7737 10021 
 7738 10021 
 7739 10019 
 7739 10020 
 7739 10021 
  

解决方案

让我们假设您有两个绑定表 supplier_approval（supplierid，rovalingid） 和 contracts_requirement（contractid，requireid），其中 approvalid 和 requireid 对应。然后您可以使用以下查询

 从Supplier_approval sa 
中选择sa.supplierid 
其中sa.approvalid IN（
从contracts_requirement中选择cr.requirementid 
 cr 
其中cr.contractid = 7736 
）
由sa.supplierid 
分组，计数为（distinct sa .approvalid）=（
选择数量（*）$ tracts_requirement中的
 cr 
其中cr.contractid = 7736 
）
  

Have a table of suppliers that have 'approval codes' (VNDAPP) and a table of contracts that have 'requirement codes' (COXA). Contracts can have any number of requirements and suppliers can have any number of approvals.

VNDAPP: (vendor approvals table)
VNUM    (vendor number)
REQMT   (approval code)

COXA:   (contract requirements table)
CONTR   (contract number)
REQMT   (requirement)

Need to find all suppliers that have approvals that, as a minimum, meet the requirements of a certain contract.

Example contract 7736 has requirements number 1 and 10. There are 27 suppliers that have both of these as a minimum. Since the requirements are variable, I cannot hard code them into the query. I'm not certain this qualifies as a relational division problem...

SELECT VNDNO
FROM VNDAPP
LEFT JOIN COXA ON VNDAPP.REQMT = COXA.REQMT
GROUP BY VNDAPP.VNDNO
HAVING Count(COXA.REQMT) = (SELECT count(*)
FROM COXA WHERE COXA.CONTR = '7736'
GROUP BY COXA.CONTR)

What am I doing wrong??

Thanks in advance!

Example data:

Contract Requirement (COXA):
CONTR   REQMT
7736    1
7736    10
7737    1
7737    4
7737    6
7738    5
7739    1



Supplier Approval (VNDAPP):
VNDNO   REQMT
10019   1
10020   1
10020   2
10020   10
10021   1
10021   4
10021   5
10021   6



Desired result:
CONTR   VNDNO
7736    10020
7737    10021
7738    10021
7739    10019
7739    10020
7739    10021  

解决方案

Lets assume that you have two binding tables supplier_approval(supplierid, approvalid) and contracts_requirement(contractid, requirementid), where approvalid and requirementid corresponds. Then you can use the following query

select sa.supplierid
from supplier_approval sa
where sa.approvalid IN (
    select cr.requirementid
    from contracts_requirement cr
    where cr.contractid = 7736
 )
group by sa.supplierid
having count(distinct sa.approvalid) = (
    select count(*)
    from contracts_requirement cr
    where cr.contractid = 7736
)

这篇关于选择至少具有与合同要求相同的批准代码的供应商的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！