How do I fix the WHEREerror when I use LEFT JOIN and ON clauses












0














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...
^









share|improve this question


















  • 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 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








  • 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 the inner join to left join including ON clause
    – mr.abdo
    Dec 27 '18 at 20:22
















0














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...
^









share|improve this question


















  • 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 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








  • 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 the inner join to left join including ON clause
    – mr.abdo
    Dec 27 '18 at 20:22














0












0








0







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...
^









share|improve this question













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






share|improve this question













share|improve this question











share|improve this question




share|improve this question










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 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








  • 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 the inner join to left join including ON clause
    – mr.abdo
    Dec 27 '18 at 20:22














  • 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 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








  • 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 the inner join to left join including ON 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












2 Answers
2






active

oldest

votes


















1














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






share|improve this answer





















  • perfect. I was using the inner join without a ON clause. Tku
    – mr.abdo
    Dec 27 '18 at 20:22



















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
;





share|improve this answer





















    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


















    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









    1














    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






    share|improve this answer





















    • perfect. I was using the inner join without a ON clause. Tku
      – mr.abdo
      Dec 27 '18 at 20:22
















    1














    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






    share|improve this answer





















    • perfect. I was using the inner join without a ON clause. Tku
      – mr.abdo
      Dec 27 '18 at 20:22














    1












    1








    1






    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






    share|improve this answer












    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







    share|improve this answer












    share|improve this answer



    share|improve this answer










    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


















    • 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













    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
    ;





    share|improve this answer


























      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
      ;





      share|improve this answer
























        1












        1








        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
        ;





        share|improve this answer












        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
        ;






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Dec 27 '18 at 21:10









        wildplasser

        30.9k53968




        30.9k53968






























            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.





            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.




            draft saved


            draft discarded














            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





















































            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







            Popular posts from this blog

            Monofisismo

            Angular Downloading a file using contenturl with Basic Authentication

            Olmecas