T-SQL 2 or more Joins on same table
up vote
0
down vote
favorite
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 at 16:15
Leon
1
|
show 2 more comments
up vote
0
down vote
favorite
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 at 16:15
Leon
1
What is the error? Is there more than 1 [status] column in Main, Goals, Result, Main? Why twice?
– Brien Foss
Nov 22 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 at 16:19
from which of the 4 joins you are doing forM, does thestatuscolumn exist? Why have the 2ndMainjoin?
– Brien Foss
Nov 22 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 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 at 18:12
|
show 2 more comments
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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 at 16:15
Leon
1
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 at 16:15
Leon
1
asked Nov 22 at 16:15
Leon
1
asked Nov 22 at 16:15
Leon
1
asked Nov 22 at 16:15
Leon
1
asked Nov 22 at 16:15
Leon
1
1
What is the error? Is there more than 1 [status] column in Main, Goals, Result, Main? Why twice?
– Brien Foss
Nov 22 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 at 16:19
from which of the 4 joins you are doing forM, does thestatuscolumn exist? Why have the 2ndMainjoin?
– Brien Foss
Nov 22 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 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 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 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 at 16:19
from which of the 4 joins you are doing forM, does thestatuscolumn exist? Why have the 2ndMainjoin?
– Brien Foss
Nov 22 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 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 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 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 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 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 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 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 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 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 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 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 at 18:12
|
show 2 more comments
1 Answer
1
active
oldest
votes
up vote
0
down vote
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 at 8:08
picklerick
33318
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
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 at 8:08
picklerick
33318
add a comment |
up vote
0
down vote
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 at 8:08
picklerick
33318
add a comment |
up vote
0
down vote
up vote
0
down vote
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 at 8:08
picklerick
33318
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 at 8:08
picklerick
33318
answered Nov 23 at 8:08
picklerick
33318
answered Nov 23 at 8:08
picklerick
33318
answered Nov 23 at 8:08
picklerick
33318
33318
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%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 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 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 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 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 at 18:12