How do I fix the WHEREerror when I use LEFT JOIN and ON clauses
I spent at least 4 hours trying to solve it, I tried a lot of solutions posted here, but I still didn't fix it.
That's my query:
SELECT * FROM l_article
INNER JOIN accounts_user
LEFT JOIN laws_marcacao ON (laws_marcacao.article_id = l_article.id
AND laws_marcacao.user_id = accounts_user.id)
WHERE l_article.law_id = 1 and accounts_user.id = 1
Error is happening near where
:
ERROR: syntax error at or near "where"
LINE 5: where l_article.law_id = 1 and accoun...
^
postgresql
add a comment |
I spent at least 4 hours trying to solve it, I tried a lot of solutions posted here, but I still didn't fix it.
That's my query:
SELECT * FROM l_article
INNER JOIN accounts_user
LEFT JOIN laws_marcacao ON (laws_marcacao.article_id = l_article.id
AND laws_marcacao.user_id = accounts_user.id)
WHERE l_article.law_id = 1 and accounts_user.id = 1
Error is happening near where
:
ERROR: syntax error at or near "where"
LINE 5: where l_article.law_id = 1 and accoun...
^
postgresql
1
NOTE:left JOINING your junction table will lead to a cathesian product. Is this your intention?
– wildplasser
Dec 27 '18 at 19:59
@wildplasser my intention is show alll_article
tuples withlaws_marcacao
andaccounts_user
associated to it. It was working good using sqlite, when I deployed it to a server that use postgres, I got this error.
– mr.abdo
Dec 27 '18 at 20:03
@wildplasser you make me think about one point and I corrected it and it works. I did the changeLEFT JOIN accounts_user ON and accounts_user.id = 1
. Tku so much
– mr.abdo
Dec 27 '18 at 20:15
1
Does it work when you leave out the completeLEFT JOIN laws_marcacao ON (laws_marcacao.article_id = l_article.id AND laws_marcacao.user_id = accounts_user.id)
? [currently it does nothing]
– wildplasser
Dec 27 '18 at 20:15
I just deleted thisand accounts_user.id = 1
and I changed theinner join
toleft join
includingON
clause
– mr.abdo
Dec 27 '18 at 20:22
add a comment |
I spent at least 4 hours trying to solve it, I tried a lot of solutions posted here, but I still didn't fix it.
That's my query:
SELECT * FROM l_article
INNER JOIN accounts_user
LEFT JOIN laws_marcacao ON (laws_marcacao.article_id = l_article.id
AND laws_marcacao.user_id = accounts_user.id)
WHERE l_article.law_id = 1 and accounts_user.id = 1
Error is happening near where
:
ERROR: syntax error at or near "where"
LINE 5: where l_article.law_id = 1 and accoun...
^
postgresql
I spent at least 4 hours trying to solve it, I tried a lot of solutions posted here, but I still didn't fix it.
That's my query:
SELECT * FROM l_article
INNER JOIN accounts_user
LEFT JOIN laws_marcacao ON (laws_marcacao.article_id = l_article.id
AND laws_marcacao.user_id = accounts_user.id)
WHERE l_article.law_id = 1 and accounts_user.id = 1
Error is happening near where
:
ERROR: syntax error at or near "where"
LINE 5: where l_article.law_id = 1 and accoun...
^
postgresql
postgresql
asked Dec 27 '18 at 19:55
mr.abdo
467
467
1
NOTE:left JOINING your junction table will lead to a cathesian product. Is this your intention?
– wildplasser
Dec 27 '18 at 19:59
@wildplasser my intention is show alll_article
tuples withlaws_marcacao
andaccounts_user
associated to it. It was working good using sqlite, when I deployed it to a server that use postgres, I got this error.
– mr.abdo
Dec 27 '18 at 20:03
@wildplasser you make me think about one point and I corrected it and it works. I did the changeLEFT JOIN accounts_user ON and accounts_user.id = 1
. Tku so much
– mr.abdo
Dec 27 '18 at 20:15
1
Does it work when you leave out the completeLEFT JOIN laws_marcacao ON (laws_marcacao.article_id = l_article.id AND laws_marcacao.user_id = accounts_user.id)
? [currently it does nothing]
– wildplasser
Dec 27 '18 at 20:15
I just deleted thisand accounts_user.id = 1
and I changed theinner join
toleft join
includingON
clause
– mr.abdo
Dec 27 '18 at 20:22
add a comment |
1
NOTE:left JOINING your junction table will lead to a cathesian product. Is this your intention?
– wildplasser
Dec 27 '18 at 19:59
@wildplasser my intention is show alll_article
tuples withlaws_marcacao
andaccounts_user
associated to it. It was working good using sqlite, when I deployed it to a server that use postgres, I got this error.
– mr.abdo
Dec 27 '18 at 20:03
@wildplasser you make me think about one point and I corrected it and it works. I did the changeLEFT JOIN accounts_user ON and accounts_user.id = 1
. Tku so much
– mr.abdo
Dec 27 '18 at 20:15
1
Does it work when you leave out the completeLEFT JOIN laws_marcacao ON (laws_marcacao.article_id = l_article.id AND laws_marcacao.user_id = accounts_user.id)
? [currently it does nothing]
– wildplasser
Dec 27 '18 at 20:15
I just deleted thisand accounts_user.id = 1
and I changed theinner join
toleft join
includingON
clause
– mr.abdo
Dec 27 '18 at 20:22
1
1
NOTE:left JOINING your junction table will lead to a cathesian product. Is this your intention?
– wildplasser
Dec 27 '18 at 19:59
NOTE:left JOINING your junction table will lead to a cathesian product. Is this your intention?
– wildplasser
Dec 27 '18 at 19:59
@wildplasser my intention is show all
l_article
tuples with laws_marcacao
and accounts_user
associated to it. It was working good using sqlite, when I deployed it to a server that use postgres, I got this error.– mr.abdo
Dec 27 '18 at 20:03
@wildplasser my intention is show all
l_article
tuples with laws_marcacao
and accounts_user
associated to it. It was working good using sqlite, when I deployed it to a server that use postgres, I got this error.– mr.abdo
Dec 27 '18 at 20:03
@wildplasser you make me think about one point and I corrected it and it works. I did the change
LEFT JOIN accounts_user ON and accounts_user.id = 1
. Tku so much– mr.abdo
Dec 27 '18 at 20:15
@wildplasser you make me think about one point and I corrected it and it works. I did the change
LEFT JOIN accounts_user ON and accounts_user.id = 1
. Tku so much– mr.abdo
Dec 27 '18 at 20:15
1
1
Does it work when you leave out the complete
LEFT JOIN laws_marcacao ON (laws_marcacao.article_id = l_article.id AND laws_marcacao.user_id = accounts_user.id)
? [currently it does nothing]– wildplasser
Dec 27 '18 at 20:15
Does it work when you leave out the complete
LEFT JOIN laws_marcacao ON (laws_marcacao.article_id = l_article.id AND laws_marcacao.user_id = accounts_user.id)
? [currently it does nothing]– wildplasser
Dec 27 '18 at 20:15
I just deleted this
and accounts_user.id = 1
and I changed the inner join
to left join
including ON
clause– mr.abdo
Dec 27 '18 at 20:22
I just deleted this
and accounts_user.id = 1
and I changed the inner join
to left join
including ON
clause– mr.abdo
Dec 27 '18 at 20:22
add a comment |
2 Answers
2
active
oldest
votes
The problem is the inner join between l_article and accounts_user, it is missing a qualified join.
syntax:
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )
T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2
https://www.postgresql.org/docs/9.2/queries-table-expressions.html
So if you want to do a cartesian product then use CROSS JOIN instead of INNER JOIN. If you want to use INNER JOIN then you will need to have an join condition such as USING or ON clause.
Check out this sqlfiddle
perfect. I was using the inner join without a ON clause. Tku
– mr.abdo
Dec 27 '18 at 20:22
add a comment |
A simple trick to keep the junction table out of the final result:
SELECT *
FROM l_article a
JOIN accounts_user u ON EXISTS (
SELECT *
FROM laws_marcacao xx
WHERE xx.article_id = a.id
AND xx.user_id = u.id
)
WHERE a.law_id = 1 AND u.id = 1
;
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%2f53950211%2fhow-do-i-fix-the-whereerror-when-i-use-left-join-and-on-clauses%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
The problem is the inner join between l_article and accounts_user, it is missing a qualified join.
syntax:
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )
T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2
https://www.postgresql.org/docs/9.2/queries-table-expressions.html
So if you want to do a cartesian product then use CROSS JOIN instead of INNER JOIN. If you want to use INNER JOIN then you will need to have an join condition such as USING or ON clause.
Check out this sqlfiddle
perfect. I was using the inner join without a ON clause. Tku
– mr.abdo
Dec 27 '18 at 20:22
add a comment |
The problem is the inner join between l_article and accounts_user, it is missing a qualified join.
syntax:
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )
T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2
https://www.postgresql.org/docs/9.2/queries-table-expressions.html
So if you want to do a cartesian product then use CROSS JOIN instead of INNER JOIN. If you want to use INNER JOIN then you will need to have an join condition such as USING or ON clause.
Check out this sqlfiddle
perfect. I was using the inner join without a ON clause. Tku
– mr.abdo
Dec 27 '18 at 20:22
add a comment |
The problem is the inner join between l_article and accounts_user, it is missing a qualified join.
syntax:
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )
T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2
https://www.postgresql.org/docs/9.2/queries-table-expressions.html
So if you want to do a cartesian product then use CROSS JOIN instead of INNER JOIN. If you want to use INNER JOIN then you will need to have an join condition such as USING or ON clause.
Check out this sqlfiddle
The problem is the inner join between l_article and accounts_user, it is missing a qualified join.
syntax:
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )
T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2
https://www.postgresql.org/docs/9.2/queries-table-expressions.html
So if you want to do a cartesian product then use CROSS JOIN instead of INNER JOIN. If you want to use INNER JOIN then you will need to have an join condition such as USING or ON clause.
Check out this sqlfiddle
answered Dec 27 '18 at 20:18
patelb
1,334716
1,334716
perfect. I was using the inner join without a ON clause. Tku
– mr.abdo
Dec 27 '18 at 20:22
add a comment |
perfect. I was using the inner join without a ON clause. Tku
– mr.abdo
Dec 27 '18 at 20:22
perfect. I was using the inner join without a ON clause. Tku
– mr.abdo
Dec 27 '18 at 20:22
perfect. I was using the inner join without a ON clause. Tku
– mr.abdo
Dec 27 '18 at 20:22
add a comment |
A simple trick to keep the junction table out of the final result:
SELECT *
FROM l_article a
JOIN accounts_user u ON EXISTS (
SELECT *
FROM laws_marcacao xx
WHERE xx.article_id = a.id
AND xx.user_id = u.id
)
WHERE a.law_id = 1 AND u.id = 1
;
add a comment |
A simple trick to keep the junction table out of the final result:
SELECT *
FROM l_article a
JOIN accounts_user u ON EXISTS (
SELECT *
FROM laws_marcacao xx
WHERE xx.article_id = a.id
AND xx.user_id = u.id
)
WHERE a.law_id = 1 AND u.id = 1
;
add a comment |
A simple trick to keep the junction table out of the final result:
SELECT *
FROM l_article a
JOIN accounts_user u ON EXISTS (
SELECT *
FROM laws_marcacao xx
WHERE xx.article_id = a.id
AND xx.user_id = u.id
)
WHERE a.law_id = 1 AND u.id = 1
;
A simple trick to keep the junction table out of the final result:
SELECT *
FROM l_article a
JOIN accounts_user u ON EXISTS (
SELECT *
FROM laws_marcacao xx
WHERE xx.article_id = a.id
AND xx.user_id = u.id
)
WHERE a.law_id = 1 AND u.id = 1
;
answered Dec 27 '18 at 21:10
wildplasser
30.9k53968
30.9k53968
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%2f53950211%2fhow-do-i-fix-the-whereerror-when-i-use-left-join-and-on-clauses%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
1
NOTE:left JOINING your junction table will lead to a cathesian product. Is this your intention?
– wildplasser
Dec 27 '18 at 19:59
@wildplasser my intention is show all
l_article
tuples withlaws_marcacao
andaccounts_user
associated to it. It was working good using sqlite, when I deployed it to a server that use postgres, I got this error.– mr.abdo
Dec 27 '18 at 20:03
@wildplasser you make me think about one point and I corrected it and it works. I did the change
LEFT JOIN accounts_user ON and accounts_user.id = 1
. Tku so much– mr.abdo
Dec 27 '18 at 20:15
1
Does it work when you leave out the complete
LEFT JOIN laws_marcacao ON (laws_marcacao.article_id = l_article.id AND laws_marcacao.user_id = accounts_user.id)
? [currently it does nothing]– wildplasser
Dec 27 '18 at 20:15
I just deleted this
and accounts_user.id = 1
and I changed theinner join
toleft join
includingON
clause– mr.abdo
Dec 27 '18 at 20:22