选择至少具有与合同要求相同的批准代码的供应商 [英] 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屋!