Multiple Inner join blank result without error
Hi dev's i'm new with "advanced" SQL I've try alone but I dont understand how to have the good result.
I'll try to take information from 4 tables in the same DB.
The first table items only have id and name.
2 others tables take the id from items to extract data.
The last tables takes one data from items_buy for print another data.
Lastly I concat 2 column from 2 DB for having a full information.
SELECT items.id, items.name, items_buy.item_cost AS item_cost, items_sales.item_price AS item_price, CONCAT(trader.name, planet.name) AS name_point
FROM ((((items
INNER JOIN items_buy ON items_buy.id = items.id)
INNER JOIN trader ON trader.id = items_buy.name_point)
INNER JOIN items_sales ON items_sales.id = items.id)
INNER JOIN planet ON planet.id = trader.planet)
WHERE items.id = 1;
I dont know how to make it work, she doesnt return an error in SQLyog or on my server.
In order:
ID / NAMEITEM / PRICE / SELLINGPRICE / NAME from concat
If you need more, some test data:
https://pastebin.com/6Bs4kbN9
mysql sql
edited Nov 25 '18 at 22:56
Martin
5,4571332
asked Nov 25 '18 at 22:39
Toquey SiGausesToquey SiGauses
32
add a comment |
Hi dev's i'm new with "advanced" SQL I've try alone but I dont understand how to have the good result.
I'll try to take information from 4 tables in the same DB.
The first table items only have id and name.
2 others tables take the id from items to extract data.
The last tables takes one data from items_buy for print another data.
Lastly I concat 2 column from 2 DB for having a full information.
SELECT items.id, items.name, items_buy.item_cost AS item_cost, items_sales.item_price AS item_price, CONCAT(trader.name, planet.name) AS name_point
FROM ((((items
INNER JOIN items_buy ON items_buy.id = items.id)
INNER JOIN trader ON trader.id = items_buy.name_point)
INNER JOIN items_sales ON items_sales.id = items.id)
INNER JOIN planet ON planet.id = trader.planet)
WHERE items.id = 1;
I dont know how to make it work, she doesnt return an error in SQLyog or on my server.
In order:
ID / NAMEITEM / PRICE / SELLINGPRICE / NAME from concat
If you need more, some test data:
https://pastebin.com/6Bs4kbN9
mysql sql
edited Nov 25 '18 at 22:56
Martin
5,4571332
asked Nov 25 '18 at 22:39
Toquey SiGausesToquey SiGauses
32
Tag your question with the database you are using. Sample data and desired results would explain what you want.
– Gordon Linoff
Nov 25 '18 at 22:39
If the issue is related to PHP please add the PHP code that reproduces it. (otherwise tag the DB version you are using, probablysql-join, and remove the PHP)
– user3783243
Nov 25 '18 at 22:43
Are you certain that all the joined tables will always contain each of the join fields? If not, you should consider aLEFT JOINinstead
– Martin
Nov 25 '18 at 22:53
@MartinParkin At least in that specific request, yes
– Toquey SiGauses
Nov 25 '18 at 23:01
@ToqueySiGauses See my answer - in fact (based on your example data) this does not actually produce a result.
– Martin
Nov 25 '18 at 23:02
add a comment |
Hi dev's i'm new with "advanced" SQL I've try alone but I dont understand how to have the good result.
I'll try to take information from 4 tables in the same DB.
The first table items only have id and name.
2 others tables take the id from items to extract data.
The last tables takes one data from items_buy for print another data.
Lastly I concat 2 column from 2 DB for having a full information.
SELECT items.id, items.name, items_buy.item_cost AS item_cost, items_sales.item_price AS item_price, CONCAT(trader.name, planet.name) AS name_point
FROM ((((items
INNER JOIN items_buy ON items_buy.id = items.id)
INNER JOIN trader ON trader.id = items_buy.name_point)
INNER JOIN items_sales ON items_sales.id = items.id)
INNER JOIN planet ON planet.id = trader.planet)
WHERE items.id = 1;
I dont know how to make it work, she doesnt return an error in SQLyog or on my server.
In order:
ID / NAMEITEM / PRICE / SELLINGPRICE / NAME from concat
If you need more, some test data:
https://pastebin.com/6Bs4kbN9
mysql sql
edited Nov 25 '18 at 22:56
Martin
5,4571332
asked Nov 25 '18 at 22:39
Toquey SiGausesToquey SiGauses
32
Hi dev's i'm new with "advanced" SQL I've try alone but I dont understand how to have the good result.
I'll try to take information from 4 tables in the same DB.
The first table items only have id and name.
2 others tables take the id from items to extract data.
The last tables takes one data from items_buy for print another data.
Lastly I concat 2 column from 2 DB for having a full information.
SELECT items.id, items.name, items_buy.item_cost AS item_cost, items_sales.item_price AS item_price, CONCAT(trader.name, planet.name) AS name_point
FROM ((((items
INNER JOIN items_buy ON items_buy.id = items.id)
INNER JOIN trader ON trader.id = items_buy.name_point)
INNER JOIN items_sales ON items_sales.id = items.id)
INNER JOIN planet ON planet.id = trader.planet)
WHERE items.id = 1;
I dont know how to make it work, she doesnt return an error in SQLyog or on my server.
In order:
ID / NAMEITEM / PRICE / SELLINGPRICE / NAME from concat
If you need more, some test data:
https://pastebin.com/6Bs4kbN9
mysql sql
mysql sql
edited Nov 25 '18 at 22:56
Martin
5,4571332
asked Nov 25 '18 at 22:39
Toquey SiGausesToquey SiGauses
32
edited Nov 25 '18 at 22:56
Martin
5,4571332
asked Nov 25 '18 at 22:39
Toquey SiGausesToquey SiGauses
32
edited Nov 25 '18 at 22:56
Martin
5,4571332
edited Nov 25 '18 at 22:56
Martin
5,4571332
edited Nov 25 '18 at 22:56
Martin
5,4571332
5,4571332
asked Nov 25 '18 at 22:39
Toquey SiGausesToquey SiGauses
32
asked Nov 25 '18 at 22:39
Toquey SiGausesToquey SiGauses
32
asked Nov 25 '18 at 22:39
Toquey SiGausesToquey SiGauses
32
32
Tag your question with the database you are using. Sample data and desired results would explain what you want.
– Gordon Linoff
Nov 25 '18 at 22:39
If the issue is related to PHP please add the PHP code that reproduces it. (otherwise tag the DB version you are using, probablysql-join, and remove the PHP)
– user3783243
Nov 25 '18 at 22:43
Are you certain that all the joined tables will always contain each of the join fields? If not, you should consider aLEFT JOINinstead
– Martin
Nov 25 '18 at 22:53
@MartinParkin At least in that specific request, yes
– Toquey SiGauses
Nov 25 '18 at 23:01
@ToqueySiGauses See my answer - in fact (based on your example data) this does not actually produce a result.
– Martin
Nov 25 '18 at 23:02
add a comment |
Tag your question with the database you are using. Sample data and desired results would explain what you want.
– Gordon Linoff
Nov 25 '18 at 22:39
If the issue is related to PHP please add the PHP code that reproduces it. (otherwise tag the DB version you are using, probablysql-join, and remove the PHP)
– user3783243
Nov 25 '18 at 22:43
Are you certain that all the joined tables will always contain each of the join fields? If not, you should consider aLEFT JOINinstead
– Martin
Nov 25 '18 at 22:53
@MartinParkin At least in that specific request, yes
– Toquey SiGauses
Nov 25 '18 at 23:01
@ToqueySiGauses See my answer - in fact (based on your example data) this does not actually produce a result.
– Martin
Nov 25 '18 at 23:02
Tag your question with the database you are using. Sample data and desired results would explain what you want.
– Gordon Linoff
Nov 25 '18 at 22:39
Tag your question with the database you are using. Sample data and desired results would explain what you want.
– Gordon Linoff
Nov 25 '18 at 22:39
If the issue is related to PHP please add the PHP code that reproduces it. (otherwise tag the DB version you are using, probably
sql-join, and remove the PHP)– user3783243
Nov 25 '18 at 22:43
If the issue is related to PHP please add the PHP code that reproduces it. (otherwise tag the DB version you are using, probably
sql-join, and remove the PHP)– user3783243
Nov 25 '18 at 22:43
Are you certain that all the joined tables will always contain each of the join fields? If not, you should consider a
LEFT JOIN instead– Martin
Nov 25 '18 at 22:53
Are you certain that all the joined tables will always contain each of the join fields? If not, you should consider a
LEFT JOIN instead– Martin
Nov 25 '18 at 22:53
@MartinParkin At least in that specific request, yes
– Toquey SiGauses
Nov 25 '18 at 23:01
@MartinParkin At least in that specific request, yes
– Toquey SiGauses
Nov 25 '18 at 23:01
@ToqueySiGauses See my answer - in fact (based on your example data) this does not actually produce a result.
– Martin
Nov 25 '18 at 23:02
@ToqueySiGauses See my answer - in fact (based on your example data) this does not actually produce a result.
– Martin
Nov 25 '18 at 23:02
add a comment |
1 Answer
1
active
oldest
votes
I've run your test data and run your script against it. As I suggested in my commment, the problem is with the INNER JOIN you are using.
I am not sure whether you are aware, but when using an INNER JOIN, if the joined table is NULL for the current row, then nothing at all will be returned.
If you modify your query to use a LEFT JOIN, you will see the results that are available regardless of whether the joined tables are NULL or otherwise:
SELECT items.id, items.name, items_buy.item_cost AS item_cost, items_sales.item_price AS item_price, CONCAT(trader.name, planet.name) AS name_point
FROM ((((items
LEFT JOIN items_buy ON items_buy.id = items.id)
LEFT JOIN trader ON trader.id = items_buy.name_point)
LEFT JOIN items_sales ON items_sales.id = items.id)
LEFT JOIN planet ON planet.id = trader.planet)
WHERE items.id = 1;
This produces:
1 Agricium 24.45 25.6 NULL
1 Agricium 24.6 25.6 NULL
The problem in the case of your example is that the join to trader or planet has no result and therefore produces no output.
answered Nov 25 '18 at 23:02
MartinMartin
5,4571332
I must say thank's to you ! That mean my concat has fail if i've a NULL return... I'll need to fix it, but your help is just great. I've a lot to learn about JOIN and SQL :'(
– Toquey SiGauses
Nov 25 '18 at 23:06
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%2f53472726%2fmultiple-inner-join-blank-result-without-error%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
I've run your test data and run your script against it. As I suggested in my commment, the problem is with the INNER JOIN you are using.
I am not sure whether you are aware, but when using an INNER JOIN, if the joined table is NULL for the current row, then nothing at all will be returned.
If you modify your query to use a LEFT JOIN, you will see the results that are available regardless of whether the joined tables are NULL or otherwise:
SELECT items.id, items.name, items_buy.item_cost AS item_cost, items_sales.item_price AS item_price, CONCAT(trader.name, planet.name) AS name_point
FROM ((((items
LEFT JOIN items_buy ON items_buy.id = items.id)
LEFT JOIN trader ON trader.id = items_buy.name_point)
LEFT JOIN items_sales ON items_sales.id = items.id)
LEFT JOIN planet ON planet.id = trader.planet)
WHERE items.id = 1;
This produces:
1 Agricium 24.45 25.6 NULL
1 Agricium 24.6 25.6 NULL
The problem in the case of your example is that the join to trader or planet has no result and therefore produces no output.
answered Nov 25 '18 at 23:02
MartinMartin
5,4571332
I must say thank's to you ! That mean my concat has fail if i've a NULL return... I'll need to fix it, but your help is just great. I've a lot to learn about JOIN and SQL :'(
– Toquey SiGauses
Nov 25 '18 at 23:06
add a comment |
I've run your test data and run your script against it. As I suggested in my commment, the problem is with the INNER JOIN you are using.
I am not sure whether you are aware, but when using an INNER JOIN, if the joined table is NULL for the current row, then nothing at all will be returned.
If you modify your query to use a LEFT JOIN, you will see the results that are available regardless of whether the joined tables are NULL or otherwise:
SELECT items.id, items.name, items_buy.item_cost AS item_cost, items_sales.item_price AS item_price, CONCAT(trader.name, planet.name) AS name_point
FROM ((((items
LEFT JOIN items_buy ON items_buy.id = items.id)
LEFT JOIN trader ON trader.id = items_buy.name_point)
LEFT JOIN items_sales ON items_sales.id = items.id)
LEFT JOIN planet ON planet.id = trader.planet)
WHERE items.id = 1;
This produces:
1 Agricium 24.45 25.6 NULL
1 Agricium 24.6 25.6 NULL
The problem in the case of your example is that the join to trader or planet has no result and therefore produces no output.
answered Nov 25 '18 at 23:02
MartinMartin
5,4571332
I must say thank's to you ! That mean my concat has fail if i've a NULL return... I'll need to fix it, but your help is just great. I've a lot to learn about JOIN and SQL :'(
– Toquey SiGauses
Nov 25 '18 at 23:06
add a comment |
I've run your test data and run your script against it. As I suggested in my commment, the problem is with the INNER JOIN you are using.
I am not sure whether you are aware, but when using an INNER JOIN, if the joined table is NULL for the current row, then nothing at all will be returned.
If you modify your query to use a LEFT JOIN, you will see the results that are available regardless of whether the joined tables are NULL or otherwise:
SELECT items.id, items.name, items_buy.item_cost AS item_cost, items_sales.item_price AS item_price, CONCAT(trader.name, planet.name) AS name_point
FROM ((((items
LEFT JOIN items_buy ON items_buy.id = items.id)
LEFT JOIN trader ON trader.id = items_buy.name_point)
LEFT JOIN items_sales ON items_sales.id = items.id)
LEFT JOIN planet ON planet.id = trader.planet)
WHERE items.id = 1;
This produces:
1 Agricium 24.45 25.6 NULL
1 Agricium 24.6 25.6 NULL
The problem in the case of your example is that the join to trader or planet has no result and therefore produces no output.
answered Nov 25 '18 at 23:02
MartinMartin
5,4571332
I've run your test data and run your script against it. As I suggested in my commment, the problem is with the INNER JOIN you are using.
I am not sure whether you are aware, but when using an INNER JOIN, if the joined table is NULL for the current row, then nothing at all will be returned.
If you modify your query to use a LEFT JOIN, you will see the results that are available regardless of whether the joined tables are NULL or otherwise:
SELECT items.id, items.name, items_buy.item_cost AS item_cost, items_sales.item_price AS item_price, CONCAT(trader.name, planet.name) AS name_point
FROM ((((items
LEFT JOIN items_buy ON items_buy.id = items.id)
LEFT JOIN trader ON trader.id = items_buy.name_point)
LEFT JOIN items_sales ON items_sales.id = items.id)
LEFT JOIN planet ON planet.id = trader.planet)
WHERE items.id = 1;
This produces:
1 Agricium 24.45 25.6 NULL
1 Agricium 24.6 25.6 NULL
The problem in the case of your example is that the join to trader or planet has no result and therefore produces no output.
answered Nov 25 '18 at 23:02
MartinMartin
5,4571332
answered Nov 25 '18 at 23:02
MartinMartin
5,4571332
answered Nov 25 '18 at 23:02
MartinMartin
5,4571332
answered Nov 25 '18 at 23:02
MartinMartin
5,4571332
5,4571332
I must say thank's to you ! That mean my concat has fail if i've a NULL return... I'll need to fix it, but your help is just great. I've a lot to learn about JOIN and SQL :'(
– Toquey SiGauses
Nov 25 '18 at 23:06
add a comment |
I must say thank's to you ! That mean my concat has fail if i've a NULL return... I'll need to fix it, but your help is just great. I've a lot to learn about JOIN and SQL :'(
– Toquey SiGauses
Nov 25 '18 at 23:06
I must say thank's to you ! That mean my concat has fail if i've a NULL return... I'll need to fix it, but your help is just great. I've a lot to learn about JOIN and SQL :'(
– Toquey SiGauses
Nov 25 '18 at 23:06
I must say thank's to you ! That mean my concat has fail if i've a NULL return... I'll need to fix it, but your help is just great. I've a lot to learn about JOIN and SQL :'(
– Toquey SiGauses
Nov 25 '18 at 23:06
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%2f53472726%2fmultiple-inner-join-blank-result-without-error%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
Tag your question with the database you are using. Sample data and desired results would explain what you want.
– Gordon Linoff
Nov 25 '18 at 22:39
If the issue is related to PHP please add the PHP code that reproduces it. (otherwise tag the DB version you are using, probably
sql-join, and remove the PHP)– user3783243
Nov 25 '18 at 22:43
Are you certain that all the joined tables will always contain each of the join fields? If not, you should consider a
LEFT JOINinstead– Martin
Nov 25 '18 at 22:53
@MartinParkin At least in that specific request, yes
– Toquey SiGauses
Nov 25 '18 at 23:01
@ToqueySiGauses See my answer - in fact (based on your example data) this does not actually produce a result.
– Martin
Nov 25 '18 at 23:02