Simple sql query with 3 tables
Consider the following database with 3 tables:
persons, cars, persons_cars (join table).
Each with the its fields described below:
persons: id, phone, address
cars: id, model, manufacturer
persons_cars: id, id_car, id_person
For current case consider all fields varchar expect the id (of course primary and foreign keys).
How would look like a query which returns me the id of the person who owns multiple cars (at least two) of the same manufacturer. (As manufacturer can be the same e.g. 'volkswagen', 'volvo', 'honda', etc.). Is it possible to do this in one query?
mysql postgresql-9.3
edited Nov 26 '18 at 7:22
Strawberry
26.8k83250
asked Nov 26 '18 at 7:14
han7elhan7el
32
add a comment |
Consider the following database with 3 tables:
persons, cars, persons_cars (join table).
Each with the its fields described below:
persons: id, phone, address
cars: id, model, manufacturer
persons_cars: id, id_car, id_person
For current case consider all fields varchar expect the id (of course primary and foreign keys).
How would look like a query which returns me the id of the person who owns multiple cars (at least two) of the same manufacturer. (As manufacturer can be the same e.g. 'volkswagen', 'volvo', 'honda', etc.). Is it possible to do this in one query?
mysql postgresql-9.3
edited Nov 26 '18 at 7:22
Strawberry
26.8k83250
asked Nov 26 '18 at 7:14
han7elhan7el
32
1
MySQL or PostgreSQL ? Please specify the exact RDBMS you are using.
– Madhur Bhaiya
Nov 26 '18 at 7:14
And go on . Try something. Note also that the id column in the third table serves no purpose (unless (model,manufacturer) is unique
– Strawberry
Nov 26 '18 at 7:17
add a comment |
Consider the following database with 3 tables:
persons, cars, persons_cars (join table).
Each with the its fields described below:
persons: id, phone, address
cars: id, model, manufacturer
persons_cars: id, id_car, id_person
For current case consider all fields varchar expect the id (of course primary and foreign keys).
How would look like a query which returns me the id of the person who owns multiple cars (at least two) of the same manufacturer. (As manufacturer can be the same e.g. 'volkswagen', 'volvo', 'honda', etc.). Is it possible to do this in one query?
mysql postgresql-9.3
edited Nov 26 '18 at 7:22
Strawberry
26.8k83250
asked Nov 26 '18 at 7:14
han7elhan7el
32
Consider the following database with 3 tables:
persons, cars, persons_cars (join table).
Each with the its fields described below:
persons: id, phone, address
cars: id, model, manufacturer
persons_cars: id, id_car, id_person
For current case consider all fields varchar expect the id (of course primary and foreign keys).
How would look like a query which returns me the id of the person who owns multiple cars (at least two) of the same manufacturer. (As manufacturer can be the same e.g. 'volkswagen', 'volvo', 'honda', etc.). Is it possible to do this in one query?
mysql postgresql-9.3
mysql postgresql-9.3
edited Nov 26 '18 at 7:22
Strawberry
26.8k83250
asked Nov 26 '18 at 7:14
han7elhan7el
32
edited Nov 26 '18 at 7:22
Strawberry
26.8k83250
asked Nov 26 '18 at 7:14
han7elhan7el
32
edited Nov 26 '18 at 7:22
Strawberry
26.8k83250
edited Nov 26 '18 at 7:22
Strawberry
26.8k83250
edited Nov 26 '18 at 7:22
Strawberry
26.8k83250
26.8k83250
asked Nov 26 '18 at 7:14
han7elhan7el
32
asked Nov 26 '18 at 7:14
han7elhan7el
32
asked Nov 26 '18 at 7:14
han7elhan7el
32
32
1
MySQL or PostgreSQL ? Please specify the exact RDBMS you are using.
– Madhur Bhaiya
Nov 26 '18 at 7:14
And go on . Try something. Note also that the id column in the third table serves no purpose (unless (model,manufacturer) is unique
– Strawberry
Nov 26 '18 at 7:17
add a comment |
1
MySQL or PostgreSQL ? Please specify the exact RDBMS you are using.
– Madhur Bhaiya
Nov 26 '18 at 7:14
And go on . Try something. Note also that the id column in the third table serves no purpose (unless (model,manufacturer) is unique
– Strawberry
Nov 26 '18 at 7:17
1
1
MySQL or PostgreSQL ? Please specify the exact RDBMS you are using.
– Madhur Bhaiya
Nov 26 '18 at 7:14
MySQL or PostgreSQL ? Please specify the exact RDBMS you are using.
– Madhur Bhaiya
Nov 26 '18 at 7:14
And go on . Try something. Note also that the id column in the third table serves no purpose (unless (model,manufacturer) is unique
– Strawberry
Nov 26 '18 at 7:17
And go on . Try something. Note also that the id column in the third table serves no purpose (unless (model,manufacturer) is unique
– Strawberry
Nov 26 '18 at 7:17
add a comment |
1 Answer
1
active
oldest
votes
Here is a simple way to do this. We can aggregate the persons_cars table by person, and then compare the total manufacturer count against the distinct manufacturer count. If the former be larger than the latter, then we may assume that a duplicate manufacturer exists, i.e. that the person owns two or more cars from a single manufacturer.
SELECT pc.id_person
FROM persons_cars pc
INNER JOIN cars c
ON pc.id_car = c.id
GROUP BY pc.id_person
HAVING COUNT(*) > COUNT(DISTINCT c.manufacturer);
You tagged with both MySQL and Postgres, though this answer should run on either database without any problems.
answered Nov 26 '18 at 7:17
Tim BiegeleisenTim Biegeleisen
235k13100159
I did not think about this approach, but it is quite a good one. Thank you!
– han7el
Nov 27 '18 at 10:20
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%2f53476260%2fsimple-sql-query-with-3-tables%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
Here is a simple way to do this. We can aggregate the persons_cars table by person, and then compare the total manufacturer count against the distinct manufacturer count. If the former be larger than the latter, then we may assume that a duplicate manufacturer exists, i.e. that the person owns two or more cars from a single manufacturer.
SELECT pc.id_person
FROM persons_cars pc
INNER JOIN cars c
ON pc.id_car = c.id
GROUP BY pc.id_person
HAVING COUNT(*) > COUNT(DISTINCT c.manufacturer);
You tagged with both MySQL and Postgres, though this answer should run on either database without any problems.
answered Nov 26 '18 at 7:17
Tim BiegeleisenTim Biegeleisen
235k13100159
I did not think about this approach, but it is quite a good one. Thank you!
– han7el
Nov 27 '18 at 10:20
add a comment |
Here is a simple way to do this. We can aggregate the persons_cars table by person, and then compare the total manufacturer count against the distinct manufacturer count. If the former be larger than the latter, then we may assume that a duplicate manufacturer exists, i.e. that the person owns two or more cars from a single manufacturer.
SELECT pc.id_person
FROM persons_cars pc
INNER JOIN cars c
ON pc.id_car = c.id
GROUP BY pc.id_person
HAVING COUNT(*) > COUNT(DISTINCT c.manufacturer);
You tagged with both MySQL and Postgres, though this answer should run on either database without any problems.
answered Nov 26 '18 at 7:17
Tim BiegeleisenTim Biegeleisen
235k13100159
I did not think about this approach, but it is quite a good one. Thank you!
– han7el
Nov 27 '18 at 10:20
add a comment |
Here is a simple way to do this. We can aggregate the persons_cars table by person, and then compare the total manufacturer count against the distinct manufacturer count. If the former be larger than the latter, then we may assume that a duplicate manufacturer exists, i.e. that the person owns two or more cars from a single manufacturer.
SELECT pc.id_person
FROM persons_cars pc
INNER JOIN cars c
ON pc.id_car = c.id
GROUP BY pc.id_person
HAVING COUNT(*) > COUNT(DISTINCT c.manufacturer);
You tagged with both MySQL and Postgres, though this answer should run on either database without any problems.
answered Nov 26 '18 at 7:17
Tim BiegeleisenTim Biegeleisen
235k13100159
Here is a simple way to do this. We can aggregate the persons_cars table by person, and then compare the total manufacturer count against the distinct manufacturer count. If the former be larger than the latter, then we may assume that a duplicate manufacturer exists, i.e. that the person owns two or more cars from a single manufacturer.
SELECT pc.id_person
FROM persons_cars pc
INNER JOIN cars c
ON pc.id_car = c.id
GROUP BY pc.id_person
HAVING COUNT(*) > COUNT(DISTINCT c.manufacturer);
You tagged with both MySQL and Postgres, though this answer should run on either database without any problems.
answered Nov 26 '18 at 7:17
Tim BiegeleisenTim Biegeleisen
235k13100159
answered Nov 26 '18 at 7:17
Tim BiegeleisenTim Biegeleisen
235k13100159
answered Nov 26 '18 at 7:17
Tim BiegeleisenTim Biegeleisen
235k13100159
answered Nov 26 '18 at 7:17
Tim BiegeleisenTim Biegeleisen
235k13100159
235k13100159
I did not think about this approach, but it is quite a good one. Thank you!
– han7el
Nov 27 '18 at 10:20
add a comment |
I did not think about this approach, but it is quite a good one. Thank you!
– han7el
Nov 27 '18 at 10:20
I did not think about this approach, but it is quite a good one. Thank you!
– han7el
Nov 27 '18 at 10:20
I did not think about this approach, but it is quite a good one. Thank you!
– han7el
Nov 27 '18 at 10:20
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%2f53476260%2fsimple-sql-query-with-3-tables%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
1
MySQL or PostgreSQL ? Please specify the exact RDBMS you are using.
– Madhur Bhaiya
Nov 26 '18 at 7:14
And go on . Try something. Note also that the id column in the third table serves no purpose (unless (model,manufacturer) is unique
– Strawberry
Nov 26 '18 at 7:17