Query that checks for subset to mysql query
SELECT PA.PATIENT_ID, PA.FIRSTNAME, PA.LASTNAME, PH.*
FROM pharmacies as PH, patients as PA
WHERE
(SELECT D.DRUG_ID
FROM drugs as D, sells as S
WHERE D.DRUG_ID = S.DRUG_ID AND PH.PHARMACY_ID = S.PHARMACY_ID and PA.TOWN = PH.TOWN )
contains
(SELECT D.DRUG_ID
FROM prescriptions as PR, drugs as D
WHERE PR.PATIENT_ID = PA.PATIENT_ID AND PR.DRUG_ID = D.DRUG_ID)
I have the above query and I would like to write it for Mysql. I want to find for all the patients, the pharmacies that are in the same town, and contain all their drugs.
mysql
asked Feb 28 '17 at 1:08
Andrew HantzosAndrew Hantzos
11
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
SELECT PA.PATIENT_ID, PA.FIRSTNAME, PA.LASTNAME, PH.*
FROM pharmacies as PH, patients as PA
WHERE
(SELECT D.DRUG_ID
FROM drugs as D, sells as S
WHERE D.DRUG_ID = S.DRUG_ID AND PH.PHARMACY_ID = S.PHARMACY_ID and PA.TOWN = PH.TOWN )
contains
(SELECT D.DRUG_ID
FROM prescriptions as PR, drugs as D
WHERE PR.PATIENT_ID = PA.PATIENT_ID AND PR.DRUG_ID = D.DRUG_ID)
I have the above query and I would like to write it for Mysql. I want to find for all the patients, the pharmacies that are in the same town, and contain all their drugs.
mysql
asked Feb 28 '17 at 1:08
Andrew HantzosAndrew Hantzos
11
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Shouldn'tDRUG_IDbe a column inprescriptions?
– Rick James
Feb 28 '17 at 3:01
add a comment |
SELECT PA.PATIENT_ID, PA.FIRSTNAME, PA.LASTNAME, PH.*
FROM pharmacies as PH, patients as PA
WHERE
(SELECT D.DRUG_ID
FROM drugs as D, sells as S
WHERE D.DRUG_ID = S.DRUG_ID AND PH.PHARMACY_ID = S.PHARMACY_ID and PA.TOWN = PH.TOWN )
contains
(SELECT D.DRUG_ID
FROM prescriptions as PR, drugs as D
WHERE PR.PATIENT_ID = PA.PATIENT_ID AND PR.DRUG_ID = D.DRUG_ID)
I have the above query and I would like to write it for Mysql. I want to find for all the patients, the pharmacies that are in the same town, and contain all their drugs.
mysql
asked Feb 28 '17 at 1:08
Andrew HantzosAndrew Hantzos
11
SELECT PA.PATIENT_ID, PA.FIRSTNAME, PA.LASTNAME, PH.*
FROM pharmacies as PH, patients as PA
WHERE
(SELECT D.DRUG_ID
FROM drugs as D, sells as S
WHERE D.DRUG_ID = S.DRUG_ID AND PH.PHARMACY_ID = S.PHARMACY_ID and PA.TOWN = PH.TOWN )
contains
(SELECT D.DRUG_ID
FROM prescriptions as PR, drugs as D
WHERE PR.PATIENT_ID = PA.PATIENT_ID AND PR.DRUG_ID = D.DRUG_ID)
I have the above query and I would like to write it for Mysql. I want to find for all the patients, the pharmacies that are in the same town, and contain all their drugs.
mysql
mysql
asked Feb 28 '17 at 1:08
Andrew HantzosAndrew Hantzos
11
asked Feb 28 '17 at 1:08
Andrew HantzosAndrew Hantzos
11
edited Feb 28 '17 at 9:33
Andrew Hantzos
asked Feb 28 '17 at 1:08
Andrew HantzosAndrew Hantzos
11
asked Feb 28 '17 at 1:08
Andrew HantzosAndrew Hantzos
11
asked Feb 28 '17 at 1:08
Andrew HantzosAndrew Hantzos
11
11
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Shouldn'tDRUG_IDbe a column inprescriptions?
– Rick James
Feb 28 '17 at 3:01
add a comment |
Shouldn'tDRUG_IDbe a column inprescriptions?
– Rick James
Feb 28 '17 at 3:01
Shouldn't
DRUG_ID be a column in prescriptions?– Rick James
Feb 28 '17 at 3:01
Shouldn't
DRUG_ID be a column in prescriptions?– Rick James
Feb 28 '17 at 3:01
add a comment |
1 Answer
1
active
oldest
votes
That is a challenge!
By comparing now many drugs a patient needs to how many a pharmacy can provide of what he needs, I think this gives a list of patient:pharmacy matches.
SELECT have.patient_id, have.pharmacy_id
FROM ( SELECT s.pharmacy_id, pr.patient_id, COUNT(*) AS ct
FROM sells AS s
JOIN prescriptions AS pr USING(drug_id)
GROUP BY s.pharmacy_id, pr.patient_id ) AS have
JOIN ( SELECT patient_id, COUNT(*) AS ct
FROM prescriptions
GROUP BY patient_id ) AS need
ON need.patient_id = have.patient_id
WHERE need.ct = have.ct
That can then be used as a derived table to get the other specifics:
SELECT PA.PATIENT_ID, PA.FIRSTNAME, PA.LASTNAME, PH.*
FROM ( the-above-query ) AS x
JOIN pharmacies as PH USING(pharmacy_id)
JOIN patients as PA USING(patient_id)
Another approach might involve using LEFT JOIN to discover which pharmacies cannot supply a needed drug for a given patient, then use another LEFT JOIN to discover which other pharmacies there are for that patient.
answered Feb 28 '17 at 5:56
Rick JamesRick James
43.6k22259
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f165672%2fquery-that-checks-for-subset-to-mysql-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
That is a challenge!
By comparing now many drugs a patient needs to how many a pharmacy can provide of what he needs, I think this gives a list of patient:pharmacy matches.
SELECT have.patient_id, have.pharmacy_id
FROM ( SELECT s.pharmacy_id, pr.patient_id, COUNT(*) AS ct
FROM sells AS s
JOIN prescriptions AS pr USING(drug_id)
GROUP BY s.pharmacy_id, pr.patient_id ) AS have
JOIN ( SELECT patient_id, COUNT(*) AS ct
FROM prescriptions
GROUP BY patient_id ) AS need
ON need.patient_id = have.patient_id
WHERE need.ct = have.ct
That can then be used as a derived table to get the other specifics:
SELECT PA.PATIENT_ID, PA.FIRSTNAME, PA.LASTNAME, PH.*
FROM ( the-above-query ) AS x
JOIN pharmacies as PH USING(pharmacy_id)
JOIN patients as PA USING(patient_id)
Another approach might involve using LEFT JOIN to discover which pharmacies cannot supply a needed drug for a given patient, then use another LEFT JOIN to discover which other pharmacies there are for that patient.
answered Feb 28 '17 at 5:56
Rick JamesRick James
43.6k22259
add a comment |
That is a challenge!
By comparing now many drugs a patient needs to how many a pharmacy can provide of what he needs, I think this gives a list of patient:pharmacy matches.
SELECT have.patient_id, have.pharmacy_id
FROM ( SELECT s.pharmacy_id, pr.patient_id, COUNT(*) AS ct
FROM sells AS s
JOIN prescriptions AS pr USING(drug_id)
GROUP BY s.pharmacy_id, pr.patient_id ) AS have
JOIN ( SELECT patient_id, COUNT(*) AS ct
FROM prescriptions
GROUP BY patient_id ) AS need
ON need.patient_id = have.patient_id
WHERE need.ct = have.ct
That can then be used as a derived table to get the other specifics:
SELECT PA.PATIENT_ID, PA.FIRSTNAME, PA.LASTNAME, PH.*
FROM ( the-above-query ) AS x
JOIN pharmacies as PH USING(pharmacy_id)
JOIN patients as PA USING(patient_id)
Another approach might involve using LEFT JOIN to discover which pharmacies cannot supply a needed drug for a given patient, then use another LEFT JOIN to discover which other pharmacies there are for that patient.
answered Feb 28 '17 at 5:56
Rick JamesRick James
43.6k22259
add a comment |
That is a challenge!
By comparing now many drugs a patient needs to how many a pharmacy can provide of what he needs, I think this gives a list of patient:pharmacy matches.
SELECT have.patient_id, have.pharmacy_id
FROM ( SELECT s.pharmacy_id, pr.patient_id, COUNT(*) AS ct
FROM sells AS s
JOIN prescriptions AS pr USING(drug_id)
GROUP BY s.pharmacy_id, pr.patient_id ) AS have
JOIN ( SELECT patient_id, COUNT(*) AS ct
FROM prescriptions
GROUP BY patient_id ) AS need
ON need.patient_id = have.patient_id
WHERE need.ct = have.ct
That can then be used as a derived table to get the other specifics:
SELECT PA.PATIENT_ID, PA.FIRSTNAME, PA.LASTNAME, PH.*
FROM ( the-above-query ) AS x
JOIN pharmacies as PH USING(pharmacy_id)
JOIN patients as PA USING(patient_id)
Another approach might involve using LEFT JOIN to discover which pharmacies cannot supply a needed drug for a given patient, then use another LEFT JOIN to discover which other pharmacies there are for that patient.
answered Feb 28 '17 at 5:56
Rick JamesRick James
43.6k22259
That is a challenge!
By comparing now many drugs a patient needs to how many a pharmacy can provide of what he needs, I think this gives a list of patient:pharmacy matches.
SELECT have.patient_id, have.pharmacy_id
FROM ( SELECT s.pharmacy_id, pr.patient_id, COUNT(*) AS ct
FROM sells AS s
JOIN prescriptions AS pr USING(drug_id)
GROUP BY s.pharmacy_id, pr.patient_id ) AS have
JOIN ( SELECT patient_id, COUNT(*) AS ct
FROM prescriptions
GROUP BY patient_id ) AS need
ON need.patient_id = have.patient_id
WHERE need.ct = have.ct
That can then be used as a derived table to get the other specifics:
SELECT PA.PATIENT_ID, PA.FIRSTNAME, PA.LASTNAME, PH.*
FROM ( the-above-query ) AS x
JOIN pharmacies as PH USING(pharmacy_id)
JOIN patients as PA USING(patient_id)
Another approach might involve using LEFT JOIN to discover which pharmacies cannot supply a needed drug for a given patient, then use another LEFT JOIN to discover which other pharmacies there are for that patient.
answered Feb 28 '17 at 5:56
Rick JamesRick James
43.6k22259
answered Feb 28 '17 at 5:56
Rick JamesRick James
43.6k22259
answered Feb 28 '17 at 5:56
Rick JamesRick James
43.6k22259
answered Feb 28 '17 at 5:56
Rick JamesRick James
43.6k22259
43.6k22259
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f165672%2fquery-that-checks-for-subset-to-mysql-query%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
Shouldn't
DRUG_IDbe a column inprescriptions?– Rick James
Feb 28 '17 at 3:01