T-SQL 2 or more Joins on same table
0
im trying to do two left joins on the same table but it won't work and i have no idea why...
Can someone help me with that?
Thats my code:
CREATE VIEW [dbo].[Sportwetten_Data]
AS
SELECT TOP 1000 Main.id AS id,
Main.date AS date,
Concat(Main.hometeam, ' - ', Main.awayteam) AS event,
Concat(Goals.homescored, '/', Goals.homeconceded, ' - ',
Goals.awayscored, '/',
Goals.awayconceded) AS goals,
LB.bet AS bet,
Main.entry AS entry,
Main.odd AS odd,
Concat(Result.homegoals, ':', Result.awaygoals) AS result,
LS.type AS status
FROM Main,
Goals,
Result,
Main AS M
LEFT JOIN Lookup_Bettype LB
ON M.bettype = LB.bettype
LEFT JOIN Lookup_Status LS
ON M.status = LS.type
WHERE Main.id = Goals.id
ORDER BY Main.id ASC
Everything works fine, except for the
LEFT JOIN Lookup_Status LS
ON M.status = LS.type
And of course
LS.type AS status
tsql join
asked Nov 22 '18 at 16:15
LeonLeon
82
|
show 2 more comments
0
im trying to do two left joins on the same table but it won't work and i have no idea why...
Can someone help me with that?
Thats my code:
CREATE VIEW [dbo].[Sportwetten_Data]
AS
SELECT TOP 1000 Main.id AS id,
Main.date AS date,
Concat(Main.hometeam, ' - ', Main.awayteam) AS event,
Concat(Goals.homescored, '/', Goals.homeconceded, ' - ',
Goals.awayscored, '/',
Goals.awayconceded) AS goals,
LB.bet AS bet,
Main.entry AS entry,
Main.odd AS odd,
Concat(Result.homegoals, ':', Result.awaygoals) AS result,
LS.type AS status
FROM Main,
Goals,
Result,
Main AS M
LEFT JOIN Lookup_Bettype LB
ON M.bettype = LB.bettype
LEFT JOIN Lookup_Status LS
ON M.status = LS.type
WHERE Main.id = Goals.id
ORDER BY Main.id ASC
Everything works fine, except for the
LEFT JOIN Lookup_Status LS
ON M.status = LS.type
And of course
LS.type AS status
tsql join
asked Nov 22 '18 at 16:15
LeonLeon
82
What is the error? Is there more than 1 [status] column in Main, Goals, Result, Main? Why twice?
– Brien Foss
Nov 22 '18 at 16:16
No idea, i use Visual Studio and i can click on "update" but then it says "Error during Batchexecution"
– Leon
Nov 22 '18 at 16:19
from which of the 4 joins you are doing forM, does thestatuscolumn exist? Why have the 2ndMainjoin?
– Brien Foss
Nov 22 '18 at 16:21
So to be clear, i never done anything with SQL i just googled all of that and tried to understand it... But the M? Again no idea The Status column exists and i thought i need the second Main
– Leon
Nov 22 '18 at 16:24
1
Your view is currently doing a cross join between goals, main and an inner join of result and main. never use implicit joins - always use explicit joins.
– Zohar Peled
Nov 22 '18 at 18:12
|
show 2 more comments
0
0
0
im trying to do two left joins on the same table but it won't work and i have no idea why...
Can someone help me with that?
Thats my code:
CREATE VIEW [dbo].[Sportwetten_Data]
AS
SELECT TOP 1000 Main.id AS id,
Main.date AS date,
Concat(Main.hometeam, ' - ', Main.awayteam) AS event,
Concat(Goals.homescored, '/', Goals.homeconceded, ' - ',
Goals.awayscored, '/',
Goals.awayconceded) AS goals,
LB.bet AS bet,
Main.entry AS entry,
Main.odd AS odd,
Concat(Result.homegoals, ':', Result.awaygoals) AS result,
LS.type AS status
FROM Main,
Goals,
Result,
Main AS M
LEFT JOIN Lookup_Bettype LB
ON M.bettype = LB.bettype
LEFT JOIN Lookup_Status LS
ON M.status = LS.type
WHERE Main.id = Goals.id
ORDER BY Main.id ASC
Everything works fine, except for the
LEFT JOIN Lookup_Status LS
ON M.status = LS.type
And of course
LS.type AS status
tsql join
asked Nov 22 '18 at 16:15
LeonLeon
82
im trying to do two left joins on the same table but it won't work and i have no idea why...
Can someone help me with that?
Thats my code:
CREATE VIEW [dbo].[Sportwetten_Data]
AS
SELECT TOP 1000 Main.id AS id,
Main.date AS date,
Concat(Main.hometeam, ' - ', Main.awayteam) AS event,
Concat(Goals.homescored, '/', Goals.homeconceded, ' - ',
Goals.awayscored, '/',
Goals.awayconceded) AS goals,
LB.bet AS bet,
Main.entry AS entry,
Main.odd AS odd,
Concat(Result.homegoals, ':', Result.awaygoals) AS result,
LS.type AS status
FROM Main,
Goals,
Result,
Main AS M
LEFT JOIN Lookup_Bettype LB
ON M.bettype = LB.bettype
LEFT JOIN Lookup_Status LS
ON M.status = LS.type
WHERE Main.id = Goals.id
ORDER BY Main.id ASC
Everything works fine, except for the
LEFT JOIN Lookup_Status LS
ON M.status = LS.type
And of course
LS.type AS status
tsql join
tsql join
asked Nov 22 '18 at 16:15
LeonLeon
82
asked Nov 22 '18 at 16:15
LeonLeon
82
asked Nov 22 '18 at 16:15
LeonLeon
82
asked Nov 22 '18 at 16:15
LeonLeon
82
asked Nov 22 '18 at 16:15
LeonLeon
82
82
What is the error? Is there more than 1 [status] column in Main, Goals, Result, Main? Why twice?
– Brien Foss
Nov 22 '18 at 16:16
No idea, i use Visual Studio and i can click on "update" but then it says "Error during Batchexecution"
– Leon
Nov 22 '18 at 16:19
from which of the 4 joins you are doing forM, does thestatuscolumn exist? Why have the 2ndMainjoin?
– Brien Foss
Nov 22 '18 at 16:21
So to be clear, i never done anything with SQL i just googled all of that and tried to understand it... But the M? Again no idea The Status column exists and i thought i need the second Main
– Leon
Nov 22 '18 at 16:24
1
Your view is currently doing a cross join between goals, main and an inner join of result and main. never use implicit joins - always use explicit joins.
– Zohar Peled
Nov 22 '18 at 18:12
|
show 2 more comments
What is the error? Is there more than 1 [status] column in Main, Goals, Result, Main? Why twice?
– Brien Foss
Nov 22 '18 at 16:16
No idea, i use Visual Studio and i can click on "update" but then it says "Error during Batchexecution"
– Leon
Nov 22 '18 at 16:19
from which of the 4 joins you are doing forM, does thestatuscolumn exist? Why have the 2ndMainjoin?
– Brien Foss
Nov 22 '18 at 16:21
So to be clear, i never done anything with SQL i just googled all of that and tried to understand it... But the M? Again no idea The Status column exists and i thought i need the second Main
– Leon
Nov 22 '18 at 16:24
1
Your view is currently doing a cross join between goals, main and an inner join of result and main. never use implicit joins - always use explicit joins.
– Zohar Peled
Nov 22 '18 at 18:12
What is the error? Is there more than 1 [status] column in Main, Goals, Result, Main? Why twice?
– Brien Foss
Nov 22 '18 at 16:16
What is the error? Is there more than 1 [status] column in Main, Goals, Result, Main? Why twice?
– Brien Foss
Nov 22 '18 at 16:16
No idea, i use Visual Studio and i can click on "update" but then it says "Error during Batchexecution"
– Leon
Nov 22 '18 at 16:19
No idea, i use Visual Studio and i can click on "update" but then it says "Error during Batchexecution"
– Leon
Nov 22 '18 at 16:19
from which of the 4 joins you are doing for
M, does the status column exist? Why have the 2nd Main join?– Brien Foss
Nov 22 '18 at 16:21
from which of the 4 joins you are doing for
M, does the status column exist? Why have the 2nd Main join?– Brien Foss
Nov 22 '18 at 16:21
So to be clear, i never done anything with SQL i just googled all of that and tried to understand it... But the M? Again no idea The Status column exists and i thought i need the second Main
– Leon
Nov 22 '18 at 16:24
So to be clear, i never done anything with SQL i just googled all of that and tried to understand it... But the M? Again no idea The Status column exists and i thought i need the second Main
– Leon
Nov 22 '18 at 16:24
1
1
Your view is currently doing a cross join between goals, main and an inner join of result and main. never use implicit joins - always use explicit joins.
– Zohar Peled
Nov 22 '18 at 18:12
Your view is currently doing a cross join between goals, main and an inner join of result and main. never use implicit joins - always use explicit joins.
– Zohar Peled
Nov 22 '18 at 18:12
|
show 2 more comments
1 Answer
1
active
oldest
votes
0
I had the same problema couple of month ago with a query with joins like yours:
FROM Main,
Goals,
Result,
Main AS M
Try to explicit the join between those tables. worked for me
answered Nov 23 '18 at 8:08
picklerickpicklerick
32918
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%2f53434843%2ft-sql-2-or-more-joins-on-same-table%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
I had the same problema couple of month ago with a query with joins like yours:
FROM Main,
Goals,
Result,
Main AS M
Try to explicit the join between those tables. worked for me
answered Nov 23 '18 at 8:08
picklerickpicklerick
32918
add a comment |
0
I had the same problema couple of month ago with a query with joins like yours:
FROM Main,
Goals,
Result,
Main AS M
Try to explicit the join between those tables. worked for me
answered Nov 23 '18 at 8:08
picklerickpicklerick
32918
add a comment |
0
0
0
I had the same problema couple of month ago with a query with joins like yours:
FROM Main,
Goals,
Result,
Main AS M
Try to explicit the join between those tables. worked for me
answered Nov 23 '18 at 8:08
picklerickpicklerick
32918
I had the same problema couple of month ago with a query with joins like yours:
FROM Main,
Goals,
Result,
Main AS M
Try to explicit the join between those tables. worked for me
answered Nov 23 '18 at 8:08
picklerickpicklerick
32918
answered Nov 23 '18 at 8:08
picklerickpicklerick
32918
answered Nov 23 '18 at 8:08
picklerickpicklerick
32918
answered Nov 23 '18 at 8:08
picklerickpicklerick
32918
32918
add a comment |
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%2f53434843%2ft-sql-2-or-more-joins-on-same-table%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
What is the error? Is there more than 1 [status] column in Main, Goals, Result, Main? Why twice?
– Brien Foss
Nov 22 '18 at 16:16
No idea, i use Visual Studio and i can click on "update" but then it says "Error during Batchexecution"
– Leon
Nov 22 '18 at 16:19
from which of the 4 joins you are doing for
M, does thestatuscolumn exist? Why have the 2ndMainjoin?– Brien Foss
Nov 22 '18 at 16:21
So to be clear, i never done anything with SQL i just googled all of that and tried to understand it... But the M? Again no idea The Status column exists and i thought i need the second Main
– Leon
Nov 22 '18 at 16:24
1
Your view is currently doing a cross join between goals, main and an inner join of result and main. never use implicit joins - always use explicit joins.
– Zohar Peled
Nov 22 '18 at 18:12