Mysql Query with two seperate join
Does anyone know the solution to this problem ?
There are 3 Tables: orders, order_groups and stores.
I want to list the orders, with the names of the stores where the order was placed, and where the order is going to be delivered.
I keep the from_store_id, and to_store_id in the order_groups table
Listing these orders would be simple, i just left join the order_groups to orders, and select the name, from_shop_id and to_shop_id, but the problem is i want the name of the stores not the id, and the store names are placed in a different table (stores)
Here is what im talking about:
Table orders
id group_id name madeup_id
1 11 johnny cash 1
2 12 billy bob 1
LEFT JOIN order_groups on order_groups.id = orders.group_id
Table order_groups
id from_store_id to_store_id
11 55 56
12 56 55
Table stores
id store_name
55 thisstore
56 thatstore
The result im looking for is:
name from_store to_store
1.johhny cash thisstore, thatstore
2.billy bob thatstore, thisstore
The statement i have yet:
SELECT
orders.name, something as from_store, something as to_store
FROM orders
LEFT JOIN order_groups on order_groups.id = orders.group_id
somehow join stores on the order_groups.from_store_id = stores.id
WHERE orders.madeup_id = 1
Any idea how to select and join the store names to the query ?
One more question. I actually want to list two kind of orders in one query from different tables too, im on the right track with this structure ?
SELECT a,b FROM a LEFT JOIN b ON b.something=a.something WHERE something
UNION ALL
SELECT a,b FROM c LEFT JOIN c ON c.something=a.something WHERE something
mysql join
edited Nov 16 '18 at 5:15
gump
7010
asked Nov 15 '18 at 15:05
Ninet9Ninet9
3116
add a comment |
Does anyone know the solution to this problem ?
There are 3 Tables: orders, order_groups and stores.
I want to list the orders, with the names of the stores where the order was placed, and where the order is going to be delivered.
I keep the from_store_id, and to_store_id in the order_groups table
Listing these orders would be simple, i just left join the order_groups to orders, and select the name, from_shop_id and to_shop_id, but the problem is i want the name of the stores not the id, and the store names are placed in a different table (stores)
Here is what im talking about:
Table orders
id group_id name madeup_id
1 11 johnny cash 1
2 12 billy bob 1
LEFT JOIN order_groups on order_groups.id = orders.group_id
Table order_groups
id from_store_id to_store_id
11 55 56
12 56 55
Table stores
id store_name
55 thisstore
56 thatstore
The result im looking for is:
name from_store to_store
1.johhny cash thisstore, thatstore
2.billy bob thatstore, thisstore
The statement i have yet:
SELECT
orders.name, something as from_store, something as to_store
FROM orders
LEFT JOIN order_groups on order_groups.id = orders.group_id
somehow join stores on the order_groups.from_store_id = stores.id
WHERE orders.madeup_id = 1
Any idea how to select and join the store names to the query ?
One more question. I actually want to list two kind of orders in one query from different tables too, im on the right track with this structure ?
SELECT a,b FROM a LEFT JOIN b ON b.something=a.something WHERE something
UNION ALL
SELECT a,b FROM c LEFT JOIN c ON c.something=a.something WHERE something
mysql join
edited Nov 16 '18 at 5:15
gump
7010
asked Nov 15 '18 at 15:05
Ninet9Ninet9
3116
2
See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Strawberry
Nov 15 '18 at 15:09
add a comment |
Does anyone know the solution to this problem ?
There are 3 Tables: orders, order_groups and stores.
I want to list the orders, with the names of the stores where the order was placed, and where the order is going to be delivered.
I keep the from_store_id, and to_store_id in the order_groups table
Listing these orders would be simple, i just left join the order_groups to orders, and select the name, from_shop_id and to_shop_id, but the problem is i want the name of the stores not the id, and the store names are placed in a different table (stores)
Here is what im talking about:
Table orders
id group_id name madeup_id
1 11 johnny cash 1
2 12 billy bob 1
LEFT JOIN order_groups on order_groups.id = orders.group_id
Table order_groups
id from_store_id to_store_id
11 55 56
12 56 55
Table stores
id store_name
55 thisstore
56 thatstore
The result im looking for is:
name from_store to_store
1.johhny cash thisstore, thatstore
2.billy bob thatstore, thisstore
The statement i have yet:
SELECT
orders.name, something as from_store, something as to_store
FROM orders
LEFT JOIN order_groups on order_groups.id = orders.group_id
somehow join stores on the order_groups.from_store_id = stores.id
WHERE orders.madeup_id = 1
Any idea how to select and join the store names to the query ?
One more question. I actually want to list two kind of orders in one query from different tables too, im on the right track with this structure ?
SELECT a,b FROM a LEFT JOIN b ON b.something=a.something WHERE something
UNION ALL
SELECT a,b FROM c LEFT JOIN c ON c.something=a.something WHERE something
mysql join
edited Nov 16 '18 at 5:15
gump
7010
asked Nov 15 '18 at 15:05
Ninet9Ninet9
3116
Does anyone know the solution to this problem ?
There are 3 Tables: orders, order_groups and stores.
I want to list the orders, with the names of the stores where the order was placed, and where the order is going to be delivered.
I keep the from_store_id, and to_store_id in the order_groups table
Listing these orders would be simple, i just left join the order_groups to orders, and select the name, from_shop_id and to_shop_id, but the problem is i want the name of the stores not the id, and the store names are placed in a different table (stores)
Here is what im talking about:
Table orders
id group_id name madeup_id
1 11 johnny cash 1
2 12 billy bob 1
LEFT JOIN order_groups on order_groups.id = orders.group_id
Table order_groups
id from_store_id to_store_id
11 55 56
12 56 55
Table stores
id store_name
55 thisstore
56 thatstore
The result im looking for is:
name from_store to_store
1.johhny cash thisstore, thatstore
2.billy bob thatstore, thisstore
The statement i have yet:
SELECT
orders.name, something as from_store, something as to_store
FROM orders
LEFT JOIN order_groups on order_groups.id = orders.group_id
somehow join stores on the order_groups.from_store_id = stores.id
WHERE orders.madeup_id = 1
Any idea how to select and join the store names to the query ?
One more question. I actually want to list two kind of orders in one query from different tables too, im on the right track with this structure ?
SELECT a,b FROM a LEFT JOIN b ON b.something=a.something WHERE something
UNION ALL
SELECT a,b FROM c LEFT JOIN c ON c.something=a.something WHERE something
mysql join
mysql join
edited Nov 16 '18 at 5:15
gump
7010
asked Nov 15 '18 at 15:05
Ninet9Ninet9
3116
edited Nov 16 '18 at 5:15
gump
7010
asked Nov 15 '18 at 15:05
Ninet9Ninet9
3116
edited Nov 16 '18 at 5:15
gump
7010
edited Nov 16 '18 at 5:15
gump
7010
edited Nov 16 '18 at 5:15
gump
7010
7010
asked Nov 15 '18 at 15:05
Ninet9Ninet9
3116
asked Nov 15 '18 at 15:05
Ninet9Ninet9
3116
asked Nov 15 '18 at 15:05
Ninet9Ninet9
3116
3116
2
See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Strawberry
Nov 15 '18 at 15:09
add a comment |
2
See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Strawberry
Nov 15 '18 at 15:09
2
2
See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Strawberry
Nov 15 '18 at 15:09
See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Strawberry
Nov 15 '18 at 15:09
add a comment |
1 Answer
1
active
oldest
votes
You only need to join 2 times the same table!
SELECT
orders.name, fromStore.store_name as from_store, toStore.store_name as to_store
FROM orders
LEFT JOIN order_groups on order_groups.id = orders.group_id
left join stores fromStore on the order_groups.from_store_id = fromStore.id
left join stores toStore on the order_groups.to_store_id = toStore.id
WHERE orders.madeup_id = 1
answered Nov 15 '18 at 16:16
DanBDanB
1,6331315
thank you DanB, i didnt know i can do that with join. Perfect answer !
– Ninet9
Nov 16 '18 at 15:25
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%2f53322330%2fmysql-query-with-two-seperate-join%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
You only need to join 2 times the same table!
SELECT
orders.name, fromStore.store_name as from_store, toStore.store_name as to_store
FROM orders
LEFT JOIN order_groups on order_groups.id = orders.group_id
left join stores fromStore on the order_groups.from_store_id = fromStore.id
left join stores toStore on the order_groups.to_store_id = toStore.id
WHERE orders.madeup_id = 1
answered Nov 15 '18 at 16:16
DanBDanB
1,6331315
thank you DanB, i didnt know i can do that with join. Perfect answer !
– Ninet9
Nov 16 '18 at 15:25
add a comment |
You only need to join 2 times the same table!
SELECT
orders.name, fromStore.store_name as from_store, toStore.store_name as to_store
FROM orders
LEFT JOIN order_groups on order_groups.id = orders.group_id
left join stores fromStore on the order_groups.from_store_id = fromStore.id
left join stores toStore on the order_groups.to_store_id = toStore.id
WHERE orders.madeup_id = 1
answered Nov 15 '18 at 16:16
DanBDanB
1,6331315
thank you DanB, i didnt know i can do that with join. Perfect answer !
– Ninet9
Nov 16 '18 at 15:25
add a comment |
You only need to join 2 times the same table!
SELECT
orders.name, fromStore.store_name as from_store, toStore.store_name as to_store
FROM orders
LEFT JOIN order_groups on order_groups.id = orders.group_id
left join stores fromStore on the order_groups.from_store_id = fromStore.id
left join stores toStore on the order_groups.to_store_id = toStore.id
WHERE orders.madeup_id = 1
answered Nov 15 '18 at 16:16
DanBDanB
1,6331315
You only need to join 2 times the same table!
SELECT
orders.name, fromStore.store_name as from_store, toStore.store_name as to_store
FROM orders
LEFT JOIN order_groups on order_groups.id = orders.group_id
left join stores fromStore on the order_groups.from_store_id = fromStore.id
left join stores toStore on the order_groups.to_store_id = toStore.id
WHERE orders.madeup_id = 1
answered Nov 15 '18 at 16:16
DanBDanB
1,6331315
answered Nov 15 '18 at 16:16
DanBDanB
1,6331315
answered Nov 15 '18 at 16:16
DanBDanB
1,6331315
answered Nov 15 '18 at 16:16
DanBDanB
1,6331315
1,6331315
thank you DanB, i didnt know i can do that with join. Perfect answer !
– Ninet9
Nov 16 '18 at 15:25
add a comment |
thank you DanB, i didnt know i can do that with join. Perfect answer !
– Ninet9
Nov 16 '18 at 15:25
thank you DanB, i didnt know i can do that with join. Perfect answer !
– Ninet9
Nov 16 '18 at 15:25
thank you DanB, i didnt know i can do that with join. Perfect answer !
– Ninet9
Nov 16 '18 at 15:25
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%2f53322330%2fmysql-query-with-two-seperate-join%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
2
See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Strawberry
Nov 15 '18 at 15:09