MYSQL SELECT with INNER JOIN and LIMIT
I'm not very familiar with MYSQL and stuck.
How can I add a LIMIT 0,100 to the following query:
SELECT
m.meta_id,
m.meta_key,
m.meta_value,
m.post_id,
p.post_title,
p.post_parent,
p.post_status
FROM
wp_abcd_postmeta m
INNER JOIN wp_abcd_posts as p
ON(
p.id = m.post_id
AND p.post_type = 'product_variation'
)
Expected result would be to get an array with 100 items. I tried adding it to the end but this only results in 4 items (I guess because the inner join filter is executed later)
If I run the query with LIMIT 100 at the end I get 4 results - without I get more than 500...
If I run the query with LIMIT 100 and ORDER BY m.post_id I get again only a few results but not 100 so to me it seems like the limit here is not quite working...
mysql
asked Jan 2 at 20:16
SebSeb
6511820
|
show 1 more comment
I'm not very familiar with MYSQL and stuck.
How can I add a LIMIT 0,100 to the following query:
SELECT
m.meta_id,
m.meta_key,
m.meta_value,
m.post_id,
p.post_title,
p.post_parent,
p.post_status
FROM
wp_abcd_postmeta m
INNER JOIN wp_abcd_posts as p
ON(
p.id = m.post_id
AND p.post_type = 'product_variation'
)
Expected result would be to get an array with 100 items. I tried adding it to the end but this only results in 4 items (I guess because the inner join filter is executed later)
If I run the query with LIMIT 100 at the end I get 4 results - without I get more than 500...
If I run the query with LIMIT 100 and ORDER BY m.post_id I get again only a few results but not 100 so to me it seems like the limit here is not quite working...
mysql
asked Jan 2 at 20:16
SebSeb
6511820
Some have suggested LIMIT. Note that LIMIT without ORDER BY is fairly meaningless. But for further help, see meta.stackoverflow.com/questions/333952/…
– Strawberry
Jan 2 at 20:56
How exactly does this help in answering my question?
– Seb
Jan 2 at 21:01
It means your question is less likely to be closed, which may prove helpful.
– Strawberry
Jan 2 at 21:15
helpful would be to ask for missing information or clarification instead...
– Seb
Jan 2 at 21:27
The accepted answer covers all of that.
– Strawberry
Jan 2 at 22:03
|
show 1 more comment
I'm not very familiar with MYSQL and stuck.
How can I add a LIMIT 0,100 to the following query:
SELECT
m.meta_id,
m.meta_key,
m.meta_value,
m.post_id,
p.post_title,
p.post_parent,
p.post_status
FROM
wp_abcd_postmeta m
INNER JOIN wp_abcd_posts as p
ON(
p.id = m.post_id
AND p.post_type = 'product_variation'
)
Expected result would be to get an array with 100 items. I tried adding it to the end but this only results in 4 items (I guess because the inner join filter is executed later)
If I run the query with LIMIT 100 at the end I get 4 results - without I get more than 500...
If I run the query with LIMIT 100 and ORDER BY m.post_id I get again only a few results but not 100 so to me it seems like the limit here is not quite working...
mysql
asked Jan 2 at 20:16
SebSeb
6511820
I'm not very familiar with MYSQL and stuck.
How can I add a LIMIT 0,100 to the following query:
SELECT
m.meta_id,
m.meta_key,
m.meta_value,
m.post_id,
p.post_title,
p.post_parent,
p.post_status
FROM
wp_abcd_postmeta m
INNER JOIN wp_abcd_posts as p
ON(
p.id = m.post_id
AND p.post_type = 'product_variation'
)
Expected result would be to get an array with 100 items. I tried adding it to the end but this only results in 4 items (I guess because the inner join filter is executed later)
If I run the query with LIMIT 100 at the end I get 4 results - without I get more than 500...
If I run the query with LIMIT 100 and ORDER BY m.post_id I get again only a few results but not 100 so to me it seems like the limit here is not quite working...
mysql
mysql
asked Jan 2 at 20:16
SebSeb
6511820
asked Jan 2 at 20:16
SebSeb
6511820
edited Jan 2 at 21:32
Seb
asked Jan 2 at 20:16
SebSeb
6511820
asked Jan 2 at 20:16
SebSeb
6511820
asked Jan 2 at 20:16
SebSeb
6511820
6511820
Some have suggested LIMIT. Note that LIMIT without ORDER BY is fairly meaningless. But for further help, see meta.stackoverflow.com/questions/333952/…
– Strawberry
Jan 2 at 20:56
How exactly does this help in answering my question?
– Seb
Jan 2 at 21:01
It means your question is less likely to be closed, which may prove helpful.
– Strawberry
Jan 2 at 21:15
helpful would be to ask for missing information or clarification instead...
– Seb
Jan 2 at 21:27
The accepted answer covers all of that.
– Strawberry
Jan 2 at 22:03
|
show 1 more comment
Some have suggested LIMIT. Note that LIMIT without ORDER BY is fairly meaningless. But for further help, see meta.stackoverflow.com/questions/333952/…
– Strawberry
Jan 2 at 20:56
How exactly does this help in answering my question?
– Seb
Jan 2 at 21:01
It means your question is less likely to be closed, which may prove helpful.
– Strawberry
Jan 2 at 21:15
helpful would be to ask for missing information or clarification instead...
– Seb
Jan 2 at 21:27
The accepted answer covers all of that.
– Strawberry
Jan 2 at 22:03
Some have suggested LIMIT. Note that LIMIT without ORDER BY is fairly meaningless. But for further help, see meta.stackoverflow.com/questions/333952/…
– Strawberry
Jan 2 at 20:56
Some have suggested LIMIT. Note that LIMIT without ORDER BY is fairly meaningless. But for further help, see meta.stackoverflow.com/questions/333952/…
– Strawberry
Jan 2 at 20:56
How exactly does this help in answering my question?
– Seb
Jan 2 at 21:01
How exactly does this help in answering my question?
– Seb
Jan 2 at 21:01
It means your question is less likely to be closed, which may prove helpful.
– Strawberry
Jan 2 at 21:15
It means your question is less likely to be closed, which may prove helpful.
– Strawberry
Jan 2 at 21:15
helpful would be to ask for missing information or clarification instead...
– Seb
Jan 2 at 21:27
helpful would be to ask for missing information or clarification instead...
– Seb
Jan 2 at 21:27
The accepted answer covers all of that.
– Strawberry
Jan 2 at 22:03
The accepted answer covers all of that.
– Strawberry
Jan 2 at 22:03
|
show 1 more comment
2 Answers
2
active
oldest
votes
SELECT
m.meta_id,
m.meta_key,
m.meta_value,
m.post_id,
p.post_title,
p.post_parent,
p.post_status
FROM
wp_abcd_postmeta m
INNER JOIN wp_abcd_posts as p
ON p.id = m.post_id
AND p.post_type = 'product_variation'
LIMIT 100
answered Jan 2 at 20:20
Ariful IslamAriful Islam
6,03962952
add a comment |
Make sure u have data in both the table,if now than you may use left or right join depending on your table data.
Than try this query
SELECT m.meta_id, m.meta_key, m.meta_value, m.post_id, p.post_title, p.post_parent,p.post_status FROM wp_abcd_postmeta m INNER JOIN wp_abcd_posts as p on p.id = m.post_id and p.post_type = 'product_variation' limit 100
answered Jan 2 at 20:28
Dhruv PatadiaDhruv Patadia
248
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%2f54012599%2fmysql-select-with-inner-join-and-limit%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
SELECT
m.meta_id,
m.meta_key,
m.meta_value,
m.post_id,
p.post_title,
p.post_parent,
p.post_status
FROM
wp_abcd_postmeta m
INNER JOIN wp_abcd_posts as p
ON p.id = m.post_id
AND p.post_type = 'product_variation'
LIMIT 100
answered Jan 2 at 20:20
Ariful IslamAriful Islam
6,03962952
add a comment |
SELECT
m.meta_id,
m.meta_key,
m.meta_value,
m.post_id,
p.post_title,
p.post_parent,
p.post_status
FROM
wp_abcd_postmeta m
INNER JOIN wp_abcd_posts as p
ON p.id = m.post_id
AND p.post_type = 'product_variation'
LIMIT 100
answered Jan 2 at 20:20
Ariful IslamAriful Islam
6,03962952
add a comment |
SELECT
m.meta_id,
m.meta_key,
m.meta_value,
m.post_id,
p.post_title,
p.post_parent,
p.post_status
FROM
wp_abcd_postmeta m
INNER JOIN wp_abcd_posts as p
ON p.id = m.post_id
AND p.post_type = 'product_variation'
LIMIT 100
answered Jan 2 at 20:20
Ariful IslamAriful Islam
6,03962952
SELECT
m.meta_id,
m.meta_key,
m.meta_value,
m.post_id,
p.post_title,
p.post_parent,
p.post_status
FROM
wp_abcd_postmeta m
INNER JOIN wp_abcd_posts as p
ON p.id = m.post_id
AND p.post_type = 'product_variation'
LIMIT 100
answered Jan 2 at 20:20
Ariful IslamAriful Islam
6,03962952
edited Jan 2 at 20:39
answered Jan 2 at 20:20
Ariful IslamAriful Islam
6,03962952
answered Jan 2 at 20:20
Ariful IslamAriful Islam
6,03962952
answered Jan 2 at 20:20
Ariful IslamAriful Islam
6,03962952
6,03962952
add a comment |
add a comment |
Make sure u have data in both the table,if now than you may use left or right join depending on your table data.
Than try this query
SELECT m.meta_id, m.meta_key, m.meta_value, m.post_id, p.post_title, p.post_parent,p.post_status FROM wp_abcd_postmeta m INNER JOIN wp_abcd_posts as p on p.id = m.post_id and p.post_type = 'product_variation' limit 100
answered Jan 2 at 20:28
Dhruv PatadiaDhruv Patadia
248
add a comment |
Make sure u have data in both the table,if now than you may use left or right join depending on your table data.
Than try this query
SELECT m.meta_id, m.meta_key, m.meta_value, m.post_id, p.post_title, p.post_parent,p.post_status FROM wp_abcd_postmeta m INNER JOIN wp_abcd_posts as p on p.id = m.post_id and p.post_type = 'product_variation' limit 100
answered Jan 2 at 20:28
Dhruv PatadiaDhruv Patadia
248
add a comment |
Make sure u have data in both the table,if now than you may use left or right join depending on your table data.
Than try this query
SELECT m.meta_id, m.meta_key, m.meta_value, m.post_id, p.post_title, p.post_parent,p.post_status FROM wp_abcd_postmeta m INNER JOIN wp_abcd_posts as p on p.id = m.post_id and p.post_type = 'product_variation' limit 100
answered Jan 2 at 20:28
Dhruv PatadiaDhruv Patadia
248
Make sure u have data in both the table,if now than you may use left or right join depending on your table data.
Than try this query
SELECT m.meta_id, m.meta_key, m.meta_value, m.post_id, p.post_title, p.post_parent,p.post_status FROM wp_abcd_postmeta m INNER JOIN wp_abcd_posts as p on p.id = m.post_id and p.post_type = 'product_variation' limit 100
answered Jan 2 at 20:28
Dhruv PatadiaDhruv Patadia
248
edited Jan 3 at 5:30
answered Jan 2 at 20:28
Dhruv PatadiaDhruv Patadia
248
answered Jan 2 at 20:28
Dhruv PatadiaDhruv Patadia
248
answered Jan 2 at 20:28
Dhruv PatadiaDhruv Patadia
248
248
add a comment |
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%2f54012599%2fmysql-select-with-inner-join-and-limit%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
Some have suggested LIMIT. Note that LIMIT without ORDER BY is fairly meaningless. But for further help, see meta.stackoverflow.com/questions/333952/…
– Strawberry
Jan 2 at 20:56
How exactly does this help in answering my question?
– Seb
Jan 2 at 21:01
It means your question is less likely to be closed, which may prove helpful.
– Strawberry
Jan 2 at 21:15
helpful would be to ask for missing information or clarification instead...
– Seb
Jan 2 at 21:27
The accepted answer covers all of that.
– Strawberry
Jan 2 at 22:03