Mixing
Select suppliers for each contract that have all codes required by the contract
I have a table of contracts that have "requirement codes" (COXA) and a table of suppliers that have "approval codes" (VNDAPP). Contracts can have any number of requirements and suppliers can have any number of approvals.
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
In another question I have received a response that works when I specify the contract number in the 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
)
The problem is I need to have matching suppliers for every contract number.
Thanks in advance!
sql db2 relational-division
edited Jan 2 at 17:40
Salman A
184k67343441
asked Jan 2 at 14:18
user3593083user3593083
807
add a comment |
I have a table of contracts that have "requirement codes" (COXA) and a table of suppliers that have "approval codes" (VNDAPP). Contracts can have any number of requirements and suppliers can have any number of approvals.
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
In another question I have received a response that works when I specify the contract number in the 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
)
The problem is I need to have matching suppliers for every contract number.
Thanks in advance!
sql db2 relational-division
edited Jan 2 at 17:40
Salman A
184k67343441
asked Jan 2 at 14:18
user3593083user3593083
807
add a comment |
I have a table of contracts that have "requirement codes" (COXA) and a table of suppliers that have "approval codes" (VNDAPP). Contracts can have any number of requirements and suppliers can have any number of approvals.
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
In another question I have received a response that works when I specify the contract number in the 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
)
The problem is I need to have matching suppliers for every contract number.
Thanks in advance!
sql db2 relational-division
edited Jan 2 at 17:40
Salman A
184k67343441
asked Jan 2 at 14:18
user3593083user3593083
807
I have a table of contracts that have "requirement codes" (COXA) and a table of suppliers that have "approval codes" (VNDAPP). Contracts can have any number of requirements and suppliers can have any number of approvals.
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
In another question I have received a response that works when I specify the contract number in the 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
)
The problem is I need to have matching suppliers for every contract number.
Thanks in advance!
sql db2 relational-division
sql db2 relational-division
edited Jan 2 at 17:40
Salman A
184k67343441
asked Jan 2 at 14:18
user3593083user3593083
807
edited Jan 2 at 17:40
Salman A
184k67343441
asked Jan 2 at 14:18
user3593083user3593083
807
edited Jan 2 at 17:40
Salman A
184k67343441
edited Jan 2 at 17:40
Salman A
184k67343441
edited Jan 2 at 17:40
Salman A
184k67343441
184k67343441
asked Jan 2 at 14:18
user3593083user3593083
807
asked Jan 2 at 14:18
user3593083user3593083
807
asked Jan 2 at 14:18
user3593083user3593083
807
807
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
You can use CROSS JOIN to generate tuples of (contracts, suppliers, contract requirement), then use LEFT JOIN to match contract requirements with supplier approvals:
SELECT
contract_requirement.contr,
suppliers.vndno,
COUNT(contract_requirement.reqmt) AS req_count,
COUNT(supplier_approval.reqmt) AS app_count
FROM contract_requirement
CROSS JOIN (
SELECT DISTINCT vndno
FROM supplier_approval
) AS suppliers
LEFT JOIN supplier_approval ON suppliers.vndno = supplier_approval.vndno AND contract_requirement.reqmt = supplier_approval.reqmt
GROUP BY contract_requirement.contr, suppliers.vndno
HAVING COUNT(contract_requirement.reqmt) = COUNT(supplier_approval.reqmt)
answered Jan 2 at 14:34
Salman ASalman A
184k67343441
Thank you sir! This works perfectly!
– user3593083
Jan 2 at 15:36
add a comment |
You can use a join and group by and then having to be sure that you have all requirements:
select cr.contr
from (select cr.*, count(*) over (partition by cr.contr) as cnt
from contract_requirement cr
) cr join
supplier_approval sa
on sa.approvalid = cr.requirementid
group by cr.contr, cr.cnt
having cr.cnt = count(*)
answered Jan 2 at 14:21
Gordon LinoffGordon Linoff
791k35314418
Access Client Solutions doesn't like the "(" character before "partition".
– user3593083
Jan 2 at 14:57
@user3593083 . . . That is the syntax for window functions, which DB2 supports.
– Gordon Linoff
Jan 2 at 14:59
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54007951%2fselect-suppliers-for-each-contract-that-have-all-codes-required-by-the-contract%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use CROSS JOIN to generate tuples of (contracts, suppliers, contract requirement), then use LEFT JOIN to match contract requirements with supplier approvals:
SELECT
contract_requirement.contr,
suppliers.vndno,
COUNT(contract_requirement.reqmt) AS req_count,
COUNT(supplier_approval.reqmt) AS app_count
FROM contract_requirement
CROSS JOIN (
SELECT DISTINCT vndno
FROM supplier_approval
) AS suppliers
LEFT JOIN supplier_approval ON suppliers.vndno = supplier_approval.vndno AND contract_requirement.reqmt = supplier_approval.reqmt
GROUP BY contract_requirement.contr, suppliers.vndno
HAVING COUNT(contract_requirement.reqmt) = COUNT(supplier_approval.reqmt)
answered Jan 2 at 14:34
Salman ASalman A
184k67343441
Thank you sir! This works perfectly!
– user3593083
Jan 2 at 15:36
add a comment |
You can use CROSS JOIN to generate tuples of (contracts, suppliers, contract requirement), then use LEFT JOIN to match contract requirements with supplier approvals:
SELECT
contract_requirement.contr,
suppliers.vndno,
COUNT(contract_requirement.reqmt) AS req_count,
COUNT(supplier_approval.reqmt) AS app_count
FROM contract_requirement
CROSS JOIN (
SELECT DISTINCT vndno
FROM supplier_approval
) AS suppliers
LEFT JOIN supplier_approval ON suppliers.vndno = supplier_approval.vndno AND contract_requirement.reqmt = supplier_approval.reqmt
GROUP BY contract_requirement.contr, suppliers.vndno
HAVING COUNT(contract_requirement.reqmt) = COUNT(supplier_approval.reqmt)
answered Jan 2 at 14:34
Salman ASalman A
184k67343441
Thank you sir! This works perfectly!
– user3593083
Jan 2 at 15:36
add a comment |
You can use CROSS JOIN to generate tuples of (contracts, suppliers, contract requirement), then use LEFT JOIN to match contract requirements with supplier approvals:
SELECT
contract_requirement.contr,
suppliers.vndno,
COUNT(contract_requirement.reqmt) AS req_count,
COUNT(supplier_approval.reqmt) AS app_count
FROM contract_requirement
CROSS JOIN (
SELECT DISTINCT vndno
FROM supplier_approval
) AS suppliers
LEFT JOIN supplier_approval ON suppliers.vndno = supplier_approval.vndno AND contract_requirement.reqmt = supplier_approval.reqmt
GROUP BY contract_requirement.contr, suppliers.vndno
HAVING COUNT(contract_requirement.reqmt) = COUNT(supplier_approval.reqmt)
answered Jan 2 at 14:34
Salman ASalman A
184k67343441
You can use CROSS JOIN to generate tuples of (contracts, suppliers, contract requirement), then use LEFT JOIN to match contract requirements with supplier approvals:
SELECT
contract_requirement.contr,
suppliers.vndno,
COUNT(contract_requirement.reqmt) AS req_count,
COUNT(supplier_approval.reqmt) AS app_count
FROM contract_requirement
CROSS JOIN (
SELECT DISTINCT vndno
FROM supplier_approval
) AS suppliers
LEFT JOIN supplier_approval ON suppliers.vndno = supplier_approval.vndno AND contract_requirement.reqmt = supplier_approval.reqmt
GROUP BY contract_requirement.contr, suppliers.vndno
HAVING COUNT(contract_requirement.reqmt) = COUNT(supplier_approval.reqmt)
answered Jan 2 at 14:34
Salman ASalman A
184k67343441
edited Jan 2 at 14:55
answered Jan 2 at 14:34
Salman ASalman A
184k67343441
answered Jan 2 at 14:34
Salman ASalman A
184k67343441
answered Jan 2 at 14:34
Salman ASalman A
184k67343441
184k67343441
Thank you sir! This works perfectly!
– user3593083
Jan 2 at 15:36
add a comment |
Thank you sir! This works perfectly!
– user3593083
Jan 2 at 15:36
Thank you sir! This works perfectly!
– user3593083
Jan 2 at 15:36
Thank you sir! This works perfectly!
– user3593083
Jan 2 at 15:36
add a comment |
You can use a join and group by and then having to be sure that you have all requirements:
select cr.contr
from (select cr.*, count(*) over (partition by cr.contr) as cnt
from contract_requirement cr
) cr join
supplier_approval sa
on sa.approvalid = cr.requirementid
group by cr.contr, cr.cnt
having cr.cnt = count(*)
answered Jan 2 at 14:21
Gordon LinoffGordon Linoff
791k35314418
Access Client Solutions doesn't like the "(" character before "partition".
– user3593083
Jan 2 at 14:57
@user3593083 . . . That is the syntax for window functions, which DB2 supports.
– Gordon Linoff
Jan 2 at 14:59
add a comment |
You can use a join and group by and then having to be sure that you have all requirements:
select cr.contr
from (select cr.*, count(*) over (partition by cr.contr) as cnt
from contract_requirement cr
) cr join
supplier_approval sa
on sa.approvalid = cr.requirementid
group by cr.contr, cr.cnt
having cr.cnt = count(*)
answered Jan 2 at 14:21
Gordon LinoffGordon Linoff
791k35314418
Access Client Solutions doesn't like the "(" character before "partition".
– user3593083
Jan 2 at 14:57
@user3593083 . . . That is the syntax for window functions, which DB2 supports.
– Gordon Linoff
Jan 2 at 14:59
add a comment |
You can use a join and group by and then having to be sure that you have all requirements:
select cr.contr
from (select cr.*, count(*) over (partition by cr.contr) as cnt
from contract_requirement cr
) cr join
supplier_approval sa
on sa.approvalid = cr.requirementid
group by cr.contr, cr.cnt
having cr.cnt = count(*)
answered Jan 2 at 14:21
Gordon LinoffGordon Linoff
791k35314418
You can use a join and group by and then having to be sure that you have all requirements:
select cr.contr
from (select cr.*, count(*) over (partition by cr.contr) as cnt
from contract_requirement cr
) cr join
supplier_approval sa
on sa.approvalid = cr.requirementid
group by cr.contr, cr.cnt
having cr.cnt = count(*)
answered Jan 2 at 14:21
Gordon LinoffGordon Linoff
791k35314418
answered Jan 2 at 14:21
Gordon LinoffGordon Linoff
791k35314418
answered Jan 2 at 14:21
Gordon LinoffGordon Linoff
791k35314418
answered Jan 2 at 14:21
Gordon LinoffGordon Linoff
791k35314418
791k35314418
Access Client Solutions doesn't like the "(" character before "partition".
– user3593083
Jan 2 at 14:57
@user3593083 . . . That is the syntax for window functions, which DB2 supports.
– Gordon Linoff
Jan 2 at 14:59
add a comment |
Access Client Solutions doesn't like the "(" character before "partition".
– user3593083
Jan 2 at 14:57
@user3593083 . . . That is the syntax for window functions, which DB2 supports.
– Gordon Linoff
Jan 2 at 14:59
Access Client Solutions doesn't like the "(" character before "partition".
– user3593083
Jan 2 at 14:57
Access Client Solutions doesn't like the "(" character before "partition".
– user3593083
Jan 2 at 14:57
@user3593083 . . . That is the syntax for window functions, which DB2 supports.
– Gordon Linoff
Jan 2 at 14:59
@user3593083 . . . That is the syntax for window functions, which DB2 supports.
– Gordon Linoff
Jan 2 at 14:59
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54007951%2fselect-suppliers-for-each-contract-that-have-all-codes-required-by-the-contract%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
