Join two tables based on a view, sql

Multi tool use
Multi tool use





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}







-1















I have the following sql situation



Table1
id name

Table2
id name

_Table1ToTable2
id, id_table1, id_table2

Result:
id, name, table2_name, table2_id


I would like to join Table1 and Table2 into a single query, but I cannot find a way to make a join between them, any ideas?










share|improve this question




















  • 2





    Sample data and expected results would be helpful...

    – sgeddes
    Jan 3 at 22:12











  • @sgeddes done!!

    – andresmijares25
    Jan 3 at 22:14











  • Sample data means actual data we can work with. Also, how do the tables relate to each other ? What DBMS are you using ?

    – Tony
    Jan 3 at 22:15













  • @Tony relationship is on the _Table1ToTable2 table view, basically, it's a 1 to 1 relation between the 2, this is a mysql table

    – andresmijares25
    Jan 3 at 22:18











  • can Table1 and Table2 have different number or rows? in the result, would you like to see nulls if either Table1 or 2 corresponding row is missing?

    – Max
    Jan 3 at 22:21


















-1















I have the following sql situation



Table1
id name

Table2
id name

_Table1ToTable2
id, id_table1, id_table2

Result:
id, name, table2_name, table2_id


I would like to join Table1 and Table2 into a single query, but I cannot find a way to make a join between them, any ideas?










share|improve this question




















  • 2





    Sample data and expected results would be helpful...

    – sgeddes
    Jan 3 at 22:12











  • @sgeddes done!!

    – andresmijares25
    Jan 3 at 22:14











  • Sample data means actual data we can work with. Also, how do the tables relate to each other ? What DBMS are you using ?

    – Tony
    Jan 3 at 22:15













  • @Tony relationship is on the _Table1ToTable2 table view, basically, it's a 1 to 1 relation between the 2, this is a mysql table

    – andresmijares25
    Jan 3 at 22:18











  • can Table1 and Table2 have different number or rows? in the result, would you like to see nulls if either Table1 or 2 corresponding row is missing?

    – Max
    Jan 3 at 22:21














-1












-1








-1








I have the following sql situation



Table1
id name

Table2
id name

_Table1ToTable2
id, id_table1, id_table2

Result:
id, name, table2_name, table2_id


I would like to join Table1 and Table2 into a single query, but I cannot find a way to make a join between them, any ideas?










share|improve this question
















I have the following sql situation



Table1
id name

Table2
id name

_Table1ToTable2
id, id_table1, id_table2

Result:
id, name, table2_name, table2_id


I would like to join Table1 and Table2 into a single query, but I cannot find a way to make a join between them, any ideas?







mysql sql






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jan 4 at 6:48









Tony

4121312




4121312










asked Jan 3 at 22:10









andresmijares25andresmijares25

62411426




62411426








  • 2





    Sample data and expected results would be helpful...

    – sgeddes
    Jan 3 at 22:12











  • @sgeddes done!!

    – andresmijares25
    Jan 3 at 22:14











  • Sample data means actual data we can work with. Also, how do the tables relate to each other ? What DBMS are you using ?

    – Tony
    Jan 3 at 22:15













  • @Tony relationship is on the _Table1ToTable2 table view, basically, it's a 1 to 1 relation between the 2, this is a mysql table

    – andresmijares25
    Jan 3 at 22:18











  • can Table1 and Table2 have different number or rows? in the result, would you like to see nulls if either Table1 or 2 corresponding row is missing?

    – Max
    Jan 3 at 22:21














  • 2





    Sample data and expected results would be helpful...

    – sgeddes
    Jan 3 at 22:12











  • @sgeddes done!!

    – andresmijares25
    Jan 3 at 22:14











  • Sample data means actual data we can work with. Also, how do the tables relate to each other ? What DBMS are you using ?

    – Tony
    Jan 3 at 22:15













  • @Tony relationship is on the _Table1ToTable2 table view, basically, it's a 1 to 1 relation between the 2, this is a mysql table

    – andresmijares25
    Jan 3 at 22:18











  • can Table1 and Table2 have different number or rows? in the result, would you like to see nulls if either Table1 or 2 corresponding row is missing?

    – Max
    Jan 3 at 22:21








2




2





Sample data and expected results would be helpful...

– sgeddes
Jan 3 at 22:12





Sample data and expected results would be helpful...

– sgeddes
Jan 3 at 22:12













@sgeddes done!!

– andresmijares25
Jan 3 at 22:14





@sgeddes done!!

– andresmijares25
Jan 3 at 22:14













Sample data means actual data we can work with. Also, how do the tables relate to each other ? What DBMS are you using ?

– Tony
Jan 3 at 22:15







Sample data means actual data we can work with. Also, how do the tables relate to each other ? What DBMS are you using ?

– Tony
Jan 3 at 22:15















@Tony relationship is on the _Table1ToTable2 table view, basically, it's a 1 to 1 relation between the 2, this is a mysql table

– andresmijares25
Jan 3 at 22:18





@Tony relationship is on the _Table1ToTable2 table view, basically, it's a 1 to 1 relation between the 2, this is a mysql table

– andresmijares25
Jan 3 at 22:18













can Table1 and Table2 have different number or rows? in the result, would you like to see nulls if either Table1 or 2 corresponding row is missing?

– Max
Jan 3 at 22:21





can Table1 and Table2 have different number or rows? in the result, would you like to see nulls if either Table1 or 2 corresponding row is missing?

– Max
Jan 3 at 22:21












3 Answers
3






active

oldest

votes


















0














It seems like you want to use bridge table Table1ToTable2 to join Table1 and Table2. This can be done simply with 2 INNER JOINs :



SELECT
tt.id AS tt_id,
t1.id AS t1_id,
t1.name AS t1_name,
t2.id AS t2_id,
t2.name AS t2_name
FROM
Table1ToTable2 AS tt
INNER JOIN Table1 AS t1
ON t1.id = tt.id_table1
INNER JOIN Table2 AS t2
ON t2.id = tt.id_table2





share|improve this answer































    0














    if there is no relationship between table1 and table 2 then,



    select table1.id, table1.name, table2.id, table2.name from
    table1, table2


    if table1 and table2 are related with ID then,



    select  table1.id, table1.name, table2.id, table2.name from
    table1
    inner join table2
    on table1.id = table2.id





    share|improve this answer































      0














      If your intention is to retrieve a result set with every possible combination of the id columns from both tables then you can do as follows:



      select id = identity (int, 1, 1), * into #a from (select distinct table1. id as id1, table2. id as id2 from table1 cross apply table2) d; select * from #a; drop table #a



      If you do not wish to use a temp table an alternative would be to use some variation of the row_number() function.



      The above solves for an assumed requirement as your question lacks context. Elaborate on your requirements and we will be able to provide you with a more relevant and detailed answer.






      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%2f54030476%2fjoin-two-tables-based-on-a-view-sql%23new-answer', 'question_page');
        }
        );

        Post as a guest















        Required, but never shown

























        3 Answers
        3






        active

        oldest

        votes








        3 Answers
        3






        active

        oldest

        votes









        active

        oldest

        votes






        active

        oldest

        votes









        0














        It seems like you want to use bridge table Table1ToTable2 to join Table1 and Table2. This can be done simply with 2 INNER JOINs :



        SELECT
        tt.id AS tt_id,
        t1.id AS t1_id,
        t1.name AS t1_name,
        t2.id AS t2_id,
        t2.name AS t2_name
        FROM
        Table1ToTable2 AS tt
        INNER JOIN Table1 AS t1
        ON t1.id = tt.id_table1
        INNER JOIN Table2 AS t2
        ON t2.id = tt.id_table2





        share|improve this answer




























          0














          It seems like you want to use bridge table Table1ToTable2 to join Table1 and Table2. This can be done simply with 2 INNER JOINs :



          SELECT
          tt.id AS tt_id,
          t1.id AS t1_id,
          t1.name AS t1_name,
          t2.id AS t2_id,
          t2.name AS t2_name
          FROM
          Table1ToTable2 AS tt
          INNER JOIN Table1 AS t1
          ON t1.id = tt.id_table1
          INNER JOIN Table2 AS t2
          ON t2.id = tt.id_table2





          share|improve this answer


























            0












            0








            0







            It seems like you want to use bridge table Table1ToTable2 to join Table1 and Table2. This can be done simply with 2 INNER JOINs :



            SELECT
            tt.id AS tt_id,
            t1.id AS t1_id,
            t1.name AS t1_name,
            t2.id AS t2_id,
            t2.name AS t2_name
            FROM
            Table1ToTable2 AS tt
            INNER JOIN Table1 AS t1
            ON t1.id = tt.id_table1
            INNER JOIN Table2 AS t2
            ON t2.id = tt.id_table2





            share|improve this answer













            It seems like you want to use bridge table Table1ToTable2 to join Table1 and Table2. This can be done simply with 2 INNER JOINs :



            SELECT
            tt.id AS tt_id,
            t1.id AS t1_id,
            t1.name AS t1_name,
            t2.id AS t2_id,
            t2.name AS t2_name
            FROM
            Table1ToTable2 AS tt
            INNER JOIN Table1 AS t1
            ON t1.id = tt.id_table1
            INNER JOIN Table2 AS t2
            ON t2.id = tt.id_table2






            share|improve this answer












            share|improve this answer



            share|improve this answer










            answered Jan 4 at 1:16









            GMBGMB

            21.7k61028




            21.7k61028

























                0














                if there is no relationship between table1 and table 2 then,



                select table1.id, table1.name, table2.id, table2.name from
                table1, table2


                if table1 and table2 are related with ID then,



                select  table1.id, table1.name, table2.id, table2.name from
                table1
                inner join table2
                on table1.id = table2.id





                share|improve this answer




























                  0














                  if there is no relationship between table1 and table 2 then,



                  select table1.id, table1.name, table2.id, table2.name from
                  table1, table2


                  if table1 and table2 are related with ID then,



                  select  table1.id, table1.name, table2.id, table2.name from
                  table1
                  inner join table2
                  on table1.id = table2.id





                  share|improve this answer


























                    0












                    0








                    0







                    if there is no relationship between table1 and table 2 then,



                    select table1.id, table1.name, table2.id, table2.name from
                    table1, table2


                    if table1 and table2 are related with ID then,



                    select  table1.id, table1.name, table2.id, table2.name from
                    table1
                    inner join table2
                    on table1.id = table2.id





                    share|improve this answer













                    if there is no relationship between table1 and table 2 then,



                    select table1.id, table1.name, table2.id, table2.name from
                    table1, table2


                    if table1 and table2 are related with ID then,



                    select  table1.id, table1.name, table2.id, table2.name from
                    table1
                    inner join table2
                    on table1.id = table2.id






                    share|improve this answer












                    share|improve this answer



                    share|improve this answer










                    answered Jan 3 at 22:22









                    Derviş KayımbaşıoğluDerviş Kayımbaşıoğlu

                    15.7k22042




                    15.7k22042























                        0














                        If your intention is to retrieve a result set with every possible combination of the id columns from both tables then you can do as follows:



                        select id = identity (int, 1, 1), * into #a from (select distinct table1. id as id1, table2. id as id2 from table1 cross apply table2) d; select * from #a; drop table #a



                        If you do not wish to use a temp table an alternative would be to use some variation of the row_number() function.



                        The above solves for an assumed requirement as your question lacks context. Elaborate on your requirements and we will be able to provide you with a more relevant and detailed answer.






                        share|improve this answer




























                          0














                          If your intention is to retrieve a result set with every possible combination of the id columns from both tables then you can do as follows:



                          select id = identity (int, 1, 1), * into #a from (select distinct table1. id as id1, table2. id as id2 from table1 cross apply table2) d; select * from #a; drop table #a



                          If you do not wish to use a temp table an alternative would be to use some variation of the row_number() function.



                          The above solves for an assumed requirement as your question lacks context. Elaborate on your requirements and we will be able to provide you with a more relevant and detailed answer.






                          share|improve this answer


























                            0












                            0








                            0







                            If your intention is to retrieve a result set with every possible combination of the id columns from both tables then you can do as follows:



                            select id = identity (int, 1, 1), * into #a from (select distinct table1. id as id1, table2. id as id2 from table1 cross apply table2) d; select * from #a; drop table #a



                            If you do not wish to use a temp table an alternative would be to use some variation of the row_number() function.



                            The above solves for an assumed requirement as your question lacks context. Elaborate on your requirements and we will be able to provide you with a more relevant and detailed answer.






                            share|improve this answer













                            If your intention is to retrieve a result set with every possible combination of the id columns from both tables then you can do as follows:



                            select id = identity (int, 1, 1), * into #a from (select distinct table1. id as id1, table2. id as id2 from table1 cross apply table2) d; select * from #a; drop table #a



                            If you do not wish to use a temp table an alternative would be to use some variation of the row_number() function.



                            The above solves for an assumed requirement as your question lacks context. Elaborate on your requirements and we will be able to provide you with a more relevant and detailed answer.







                            share|improve this answer












                            share|improve this answer



                            share|improve this answer










                            answered Jan 3 at 22:22









                            Dale LudwigDale Ludwig

                            815




                            815






























                                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














                                StackExchange.ready(
                                function () {
                                StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54030476%2fjoin-two-tables-based-on-a-view-sql%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







                                UAQ4X iqMU j aIBg7Eo8,RIQhPzHO14WrMVN
                                qUxjB0uptoMUcMeIDial WRg,RL3noFODH55s2TZ3G9RA,jssfDl,OFcCP,2eKiawEr8j2gP9F vxpNJEhp52pdO

                                Popular posts from this blog

                                Monofisismo

                                Angular Downloading a file using contenturl with Basic Authentication

                                Olmecas