SQL Division Operator and implementation in Mysql
In a database i have some cars that are rented by customers. I want to find the cars that were rented by All Customers, and display their PlateNr. In other words i want to make SQL Division in MySql.
My Database is like this:
Customer
ID,Name
1 , John
2 , Scott
Car
PlateNr,Colour
1111 , red
2222 , black
Rents
ID , PlateNr , Date
1, 1111, 2010-01-01
1, 1111, 2010-02-01
2, 1111, 2010-03-02
2, 2222, 2010-01-02
Following some instructions i have the following query, but it doesn't work to find the car that was rented by all customers (correct result should be Platenr=1111). What is wrong with the query?
SELECT PlateNr
FROM rents as R1
WHERE NOT EXISTS
(SELECT car.PlateNr
FROM car
WHERE NOT EXISTS
(SELECT rents.PlateNr
FROM rents
WHERE rents.PlateNr=R1.PlateNr));
mysql
asked Nov 14 '18 at 7:17
ritgeoritgeo
416
add a comment |
In a database i have some cars that are rented by customers. I want to find the cars that were rented by All Customers, and display their PlateNr. In other words i want to make SQL Division in MySql.
My Database is like this:
Customer
ID,Name
1 , John
2 , Scott
Car
PlateNr,Colour
1111 , red
2222 , black
Rents
ID , PlateNr , Date
1, 1111, 2010-01-01
1, 1111, 2010-02-01
2, 1111, 2010-03-02
2, 2222, 2010-01-02
Following some instructions i have the following query, but it doesn't work to find the car that was rented by all customers (correct result should be Platenr=1111). What is wrong with the query?
SELECT PlateNr
FROM rents as R1
WHERE NOT EXISTS
(SELECT car.PlateNr
FROM car
WHERE NOT EXISTS
(SELECT rents.PlateNr
FROM rents
WHERE rents.PlateNr=R1.PlateNr));
mysql
asked Nov 14 '18 at 7:17
ritgeoritgeo
416
add a comment |
In a database i have some cars that are rented by customers. I want to find the cars that were rented by All Customers, and display their PlateNr. In other words i want to make SQL Division in MySql.
My Database is like this:
Customer
ID,Name
1 , John
2 , Scott
Car
PlateNr,Colour
1111 , red
2222 , black
Rents
ID , PlateNr , Date
1, 1111, 2010-01-01
1, 1111, 2010-02-01
2, 1111, 2010-03-02
2, 2222, 2010-01-02
Following some instructions i have the following query, but it doesn't work to find the car that was rented by all customers (correct result should be Platenr=1111). What is wrong with the query?
SELECT PlateNr
FROM rents as R1
WHERE NOT EXISTS
(SELECT car.PlateNr
FROM car
WHERE NOT EXISTS
(SELECT rents.PlateNr
FROM rents
WHERE rents.PlateNr=R1.PlateNr));
mysql
asked Nov 14 '18 at 7:17
ritgeoritgeo
416
In a database i have some cars that are rented by customers. I want to find the cars that were rented by All Customers, and display their PlateNr. In other words i want to make SQL Division in MySql.
My Database is like this:
Customer
ID,Name
1 , John
2 , Scott
Car
PlateNr,Colour
1111 , red
2222 , black
Rents
ID , PlateNr , Date
1, 1111, 2010-01-01
1, 1111, 2010-02-01
2, 1111, 2010-03-02
2, 2222, 2010-01-02
Following some instructions i have the following query, but it doesn't work to find the car that was rented by all customers (correct result should be Platenr=1111). What is wrong with the query?
SELECT PlateNr
FROM rents as R1
WHERE NOT EXISTS
(SELECT car.PlateNr
FROM car
WHERE NOT EXISTS
(SELECT rents.PlateNr
FROM rents
WHERE rents.PlateNr=R1.PlateNr));
mysql
mysql
asked Nov 14 '18 at 7:17
ritgeoritgeo
416
asked Nov 14 '18 at 7:17
ritgeoritgeo
416
asked Nov 14 '18 at 7:17
ritgeoritgeo
416
asked Nov 14 '18 at 7:17
ritgeoritgeo
416
asked Nov 14 '18 at 7:17
ritgeoritgeo
416
416
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
I would write this query as:
SELECT PlateNr
FROM Rents
GROUP BY PlateNr
HAVING COUNT(DISTINCT ID) = (SELECT COUNT(*) FROM Customer);
In plain English, this says to find all plates whose distinct count of renting customers matches the total number of customers. I assume here that ID is a unique column in the Customers table.
answered Nov 14 '18 at 7:26
Tim BiegeleisenTim Biegeleisen
223k1391143
add a comment |
With this query you can see which customer rented which car and how many times, ıf you can ask something else you can add a comment
Select Customer.Name,Car.PlateNr,count(*) as Count
from Customer inner join Rents On Rents.ID=Customer.ID
inner join Car On Car.PlateNr=Rents.PlateNr
group by Customer.Name,Car.PlateNr
having count(*)>1
answered Nov 14 '18 at 7:38
yusuf hayırseveryusuf hayırsever
1877
Thanks Yusuf. It doesnt work, bu if i replace Car,PlateNr with Car.PlateNr it works as you describe.
– ritgeo
Nov 14 '18 at 7:52
Yes I corrected it,
– yusuf hayırsever
Nov 14 '18 at 7:53
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%2f53294924%2fsql-division-operator-and-implementation-in-mysql%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
I would write this query as:
SELECT PlateNr
FROM Rents
GROUP BY PlateNr
HAVING COUNT(DISTINCT ID) = (SELECT COUNT(*) FROM Customer);
In plain English, this says to find all plates whose distinct count of renting customers matches the total number of customers. I assume here that ID is a unique column in the Customers table.
answered Nov 14 '18 at 7:26
Tim BiegeleisenTim Biegeleisen
223k1391143
add a comment |
I would write this query as:
SELECT PlateNr
FROM Rents
GROUP BY PlateNr
HAVING COUNT(DISTINCT ID) = (SELECT COUNT(*) FROM Customer);
In plain English, this says to find all plates whose distinct count of renting customers matches the total number of customers. I assume here that ID is a unique column in the Customers table.
answered Nov 14 '18 at 7:26
Tim BiegeleisenTim Biegeleisen
223k1391143
add a comment |
I would write this query as:
SELECT PlateNr
FROM Rents
GROUP BY PlateNr
HAVING COUNT(DISTINCT ID) = (SELECT COUNT(*) FROM Customer);
In plain English, this says to find all plates whose distinct count of renting customers matches the total number of customers. I assume here that ID is a unique column in the Customers table.
answered Nov 14 '18 at 7:26
Tim BiegeleisenTim Biegeleisen
223k1391143
I would write this query as:
SELECT PlateNr
FROM Rents
GROUP BY PlateNr
HAVING COUNT(DISTINCT ID) = (SELECT COUNT(*) FROM Customer);
In plain English, this says to find all plates whose distinct count of renting customers matches the total number of customers. I assume here that ID is a unique column in the Customers table.
answered Nov 14 '18 at 7:26
Tim BiegeleisenTim Biegeleisen
223k1391143
answered Nov 14 '18 at 7:26
Tim BiegeleisenTim Biegeleisen
223k1391143
answered Nov 14 '18 at 7:26
Tim BiegeleisenTim Biegeleisen
223k1391143
answered Nov 14 '18 at 7:26
Tim BiegeleisenTim Biegeleisen
223k1391143
223k1391143
add a comment |
add a comment |
With this query you can see which customer rented which car and how many times, ıf you can ask something else you can add a comment
Select Customer.Name,Car.PlateNr,count(*) as Count
from Customer inner join Rents On Rents.ID=Customer.ID
inner join Car On Car.PlateNr=Rents.PlateNr
group by Customer.Name,Car.PlateNr
having count(*)>1
answered Nov 14 '18 at 7:38
yusuf hayırseveryusuf hayırsever
1877
Thanks Yusuf. It doesnt work, bu if i replace Car,PlateNr with Car.PlateNr it works as you describe.
– ritgeo
Nov 14 '18 at 7:52
Yes I corrected it,
– yusuf hayırsever
Nov 14 '18 at 7:53
add a comment |
With this query you can see which customer rented which car and how many times, ıf you can ask something else you can add a comment
Select Customer.Name,Car.PlateNr,count(*) as Count
from Customer inner join Rents On Rents.ID=Customer.ID
inner join Car On Car.PlateNr=Rents.PlateNr
group by Customer.Name,Car.PlateNr
having count(*)>1
answered Nov 14 '18 at 7:38
yusuf hayırseveryusuf hayırsever
1877
Thanks Yusuf. It doesnt work, bu if i replace Car,PlateNr with Car.PlateNr it works as you describe.
– ritgeo
Nov 14 '18 at 7:52
Yes I corrected it,
– yusuf hayırsever
Nov 14 '18 at 7:53
add a comment |
With this query you can see which customer rented which car and how many times, ıf you can ask something else you can add a comment
Select Customer.Name,Car.PlateNr,count(*) as Count
from Customer inner join Rents On Rents.ID=Customer.ID
inner join Car On Car.PlateNr=Rents.PlateNr
group by Customer.Name,Car.PlateNr
having count(*)>1
answered Nov 14 '18 at 7:38
yusuf hayırseveryusuf hayırsever
1877
With this query you can see which customer rented which car and how many times, ıf you can ask something else you can add a comment
Select Customer.Name,Car.PlateNr,count(*) as Count
from Customer inner join Rents On Rents.ID=Customer.ID
inner join Car On Car.PlateNr=Rents.PlateNr
group by Customer.Name,Car.PlateNr
having count(*)>1
answered Nov 14 '18 at 7:38
yusuf hayırseveryusuf hayırsever
1877
edited Nov 14 '18 at 7:53
answered Nov 14 '18 at 7:38
yusuf hayırseveryusuf hayırsever
1877
answered Nov 14 '18 at 7:38
yusuf hayırseveryusuf hayırsever
1877
answered Nov 14 '18 at 7:38
yusuf hayırseveryusuf hayırsever
1877
1877
Thanks Yusuf. It doesnt work, bu if i replace Car,PlateNr with Car.PlateNr it works as you describe.
– ritgeo
Nov 14 '18 at 7:52
Yes I corrected it,
– yusuf hayırsever
Nov 14 '18 at 7:53
add a comment |
Thanks Yusuf. It doesnt work, bu if i replace Car,PlateNr with Car.PlateNr it works as you describe.
– ritgeo
Nov 14 '18 at 7:52
Yes I corrected it,
– yusuf hayırsever
Nov 14 '18 at 7:53
Thanks Yusuf. It doesnt work, bu if i replace Car,PlateNr with Car.PlateNr it works as you describe.
– ritgeo
Nov 14 '18 at 7:52
Thanks Yusuf. It doesnt work, bu if i replace Car,PlateNr with Car.PlateNr it works as you describe.
– ritgeo
Nov 14 '18 at 7:52
Yes I corrected it,
– yusuf hayırsever
Nov 14 '18 at 7:53
Yes I corrected it,
– yusuf hayırsever
Nov 14 '18 at 7:53
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%2f53294924%2fsql-division-operator-and-implementation-in-mysql%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
