MySQL Query Left Join Problem To Get Data
0
I want show data liker here :
id_produk | jumlah_liker | id_user
1 | 5 | 1
2 | 0 | 1
Review Table produk
pr_id | nama
1 | Milk
2 | Choco
Review Table liker
id_produk | id_user
1 | 1
1 | 1
1 | 1
1 | 1
1 | 1
But i got sql record
id_produk | jumlah_liker | id_user
1 | 1 | 1 //the problem here
2 | 0 | 1
SELECT liker.id_produk, COUNT(liker.id_produk) AS jumlah_like, liker.id_user
FROM produk
LEFT OUTER JOIN liker ON liker.id_produk = produk.pr_id AND liker.id_user = 1
GROUP BY produk.pr_id
mysql
edited Nov 26 '18 at 14:06
Valerian Pereira
5241513
asked Nov 26 '18 at 9:25
Nindy HapsariNindy Hapsari
33
add a comment |
0
I want show data liker here :
id_produk | jumlah_liker | id_user
1 | 5 | 1
2 | 0 | 1
Review Table produk
pr_id | nama
1 | Milk
2 | Choco
Review Table liker
id_produk | id_user
1 | 1
1 | 1
1 | 1
1 | 1
1 | 1
But i got sql record
id_produk | jumlah_liker | id_user
1 | 1 | 1 //the problem here
2 | 0 | 1
SELECT liker.id_produk, COUNT(liker.id_produk) AS jumlah_like, liker.id_user
FROM produk
LEFT OUTER JOIN liker ON liker.id_produk = produk.pr_id AND liker.id_user = 1
GROUP BY produk.pr_id
mysql
edited Nov 26 '18 at 14:06
Valerian Pereira
5241513
asked Nov 26 '18 at 9:25
Nindy HapsariNindy Hapsari
33
add a comment |
0
0
0
I want show data liker here :
id_produk | jumlah_liker | id_user
1 | 5 | 1
2 | 0 | 1
Review Table produk
pr_id | nama
1 | Milk
2 | Choco
Review Table liker
id_produk | id_user
1 | 1
1 | 1
1 | 1
1 | 1
1 | 1
But i got sql record
id_produk | jumlah_liker | id_user
1 | 1 | 1 //the problem here
2 | 0 | 1
SELECT liker.id_produk, COUNT(liker.id_produk) AS jumlah_like, liker.id_user
FROM produk
LEFT OUTER JOIN liker ON liker.id_produk = produk.pr_id AND liker.id_user = 1
GROUP BY produk.pr_id
mysql
edited Nov 26 '18 at 14:06
Valerian Pereira
5241513
asked Nov 26 '18 at 9:25
Nindy HapsariNindy Hapsari
33
I want show data liker here :
id_produk | jumlah_liker | id_user
1 | 5 | 1
2 | 0 | 1
Review Table produk
pr_id | nama
1 | Milk
2 | Choco
Review Table liker
id_produk | id_user
1 | 1
1 | 1
1 | 1
1 | 1
1 | 1
But i got sql record
id_produk | jumlah_liker | id_user
1 | 1 | 1 //the problem here
2 | 0 | 1
SELECT liker.id_produk, COUNT(liker.id_produk) AS jumlah_like, liker.id_user
FROM produk
LEFT OUTER JOIN liker ON liker.id_produk = produk.pr_id AND liker.id_user = 1
GROUP BY produk.pr_id
mysql
mysql
edited Nov 26 '18 at 14:06
Valerian Pereira
5241513
asked Nov 26 '18 at 9:25
Nindy HapsariNindy Hapsari
33
edited Nov 26 '18 at 14:06
Valerian Pereira
5241513
asked Nov 26 '18 at 9:25
Nindy HapsariNindy Hapsari
33
edited Nov 26 '18 at 14:06
Valerian Pereira
5241513
edited Nov 26 '18 at 14:06
Valerian Pereira
5241513
edited Nov 26 '18 at 14:06
Valerian Pereira
5241513
5241513
asked Nov 26 '18 at 9:25
Nindy HapsariNindy Hapsari
33
asked Nov 26 '18 at 9:25
Nindy HapsariNindy Hapsari
33
asked Nov 26 '18 at 9:25
Nindy HapsariNindy Hapsari
33
33
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
0
You need to GROUP BY both the id_produk and id_user columns. Also, you should be selecting and aggregating on the id from the produk table. The whole point of doing a left join from produk is to ensure that each product will always appear in the output.
SELECT
p.pr_id, -- NOT liker.id_produk
COUNT(l.id_produk) AS jumlah_like,
l.id_user
FROM produk p
LEFT JOIN liker l
ON l.id_produk = p.pr_id AND l.id_user = 1
GROUP BY
p.pr_id,
l.id_user;
Demo
answered Nov 26 '18 at 9:33
Tim BiegeleisenTim Biegeleisen
235k13100158
Same record i got sir
– Nindy Hapsari
Nov 26 '18 at 9:35
It works now, sorry did not test at first.
– Tim Biegeleisen
Nov 26 '18 at 9:38
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
});
}
});
draft saved
draft discarded
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%2f53478041%2fmysql-query-left-join-problem-to-get-data%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
0
You need to GROUP BY both the id_produk and id_user columns. Also, you should be selecting and aggregating on the id from the produk table. The whole point of doing a left join from produk is to ensure that each product will always appear in the output.
SELECT
p.pr_id, -- NOT liker.id_produk
COUNT(l.id_produk) AS jumlah_like,
l.id_user
FROM produk p
LEFT JOIN liker l
ON l.id_produk = p.pr_id AND l.id_user = 1
GROUP BY
p.pr_id,
l.id_user;
Demo
answered Nov 26 '18 at 9:33
Tim BiegeleisenTim Biegeleisen
235k13100158
Same record i got sir
– Nindy Hapsari
Nov 26 '18 at 9:35
It works now, sorry did not test at first.
– Tim Biegeleisen
Nov 26 '18 at 9:38
add a comment |
0
You need to GROUP BY both the id_produk and id_user columns. Also, you should be selecting and aggregating on the id from the produk table. The whole point of doing a left join from produk is to ensure that each product will always appear in the output.
SELECT
p.pr_id, -- NOT liker.id_produk
COUNT(l.id_produk) AS jumlah_like,
l.id_user
FROM produk p
LEFT JOIN liker l
ON l.id_produk = p.pr_id AND l.id_user = 1
GROUP BY
p.pr_id,
l.id_user;
Demo
answered Nov 26 '18 at 9:33
Tim BiegeleisenTim Biegeleisen
235k13100158
Same record i got sir
– Nindy Hapsari
Nov 26 '18 at 9:35
It works now, sorry did not test at first.
– Tim Biegeleisen
Nov 26 '18 at 9:38
add a comment |
0
0
0
You need to GROUP BY both the id_produk and id_user columns. Also, you should be selecting and aggregating on the id from the produk table. The whole point of doing a left join from produk is to ensure that each product will always appear in the output.
SELECT
p.pr_id, -- NOT liker.id_produk
COUNT(l.id_produk) AS jumlah_like,
l.id_user
FROM produk p
LEFT JOIN liker l
ON l.id_produk = p.pr_id AND l.id_user = 1
GROUP BY
p.pr_id,
l.id_user;
Demo
answered Nov 26 '18 at 9:33
Tim BiegeleisenTim Biegeleisen
235k13100158
You need to GROUP BY both the id_produk and id_user columns. Also, you should be selecting and aggregating on the id from the produk table. The whole point of doing a left join from produk is to ensure that each product will always appear in the output.
SELECT
p.pr_id, -- NOT liker.id_produk
COUNT(l.id_produk) AS jumlah_like,
l.id_user
FROM produk p
LEFT JOIN liker l
ON l.id_produk = p.pr_id AND l.id_user = 1
GROUP BY
p.pr_id,
l.id_user;
Demo
answered Nov 26 '18 at 9:33
Tim BiegeleisenTim Biegeleisen
235k13100158
edited Nov 26 '18 at 9:44
answered Nov 26 '18 at 9:33
Tim BiegeleisenTim Biegeleisen
235k13100158
answered Nov 26 '18 at 9:33
Tim BiegeleisenTim Biegeleisen
235k13100158
answered Nov 26 '18 at 9:33
Tim BiegeleisenTim Biegeleisen
235k13100158
235k13100158
Same record i got sir
– Nindy Hapsari
Nov 26 '18 at 9:35
It works now, sorry did not test at first.
– Tim Biegeleisen
Nov 26 '18 at 9:38
add a comment |
Same record i got sir
– Nindy Hapsari
Nov 26 '18 at 9:35
It works now, sorry did not test at first.
– Tim Biegeleisen
Nov 26 '18 at 9:38
Same record i got sir
– Nindy Hapsari
Nov 26 '18 at 9:35
Same record i got sir
– Nindy Hapsari
Nov 26 '18 at 9:35
It works now, sorry did not test at first.
– Tim Biegeleisen
Nov 26 '18 at 9:38
It works now, sorry did not test at first.
– Tim Biegeleisen
Nov 26 '18 at 9:38
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53478041%2fmysql-query-left-join-problem-to-get-data%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
