Join two tables in SQL Server










0















I have to tables



Customer



CustomerID Name Surname
---------------------------------
1 Adam Test
2 Robert Test2
3 John Test3


CustomerAddress



CustomerAddressId CustomerId AddressId
-------------------------------------------
1 1 1
2 1 2
3 1 3
4 2 6
5 2 7
6 2 8


I want to select CustomerId from these two tables.



I wrote this query but it multiplies my records.










share|improve this question



















  • 1





    What query did you write? It would be helpful for us to have the query, expected output, etc.

    – Jeffrey Van Laethem
    Nov 15 '18 at 15:53











  • you have 2 proposed answers below check them out and either will get you the non multiplied answers your looking for. @adamek339

    – junketsu
    Nov 15 '18 at 18:39















0















I have to tables



Customer



CustomerID Name Surname
---------------------------------
1 Adam Test
2 Robert Test2
3 John Test3


CustomerAddress



CustomerAddressId CustomerId AddressId
-------------------------------------------
1 1 1
2 1 2
3 1 3
4 2 6
5 2 7
6 2 8


I want to select CustomerId from these two tables.



I wrote this query but it multiplies my records.










share|improve this question



















  • 1





    What query did you write? It would be helpful for us to have the query, expected output, etc.

    – Jeffrey Van Laethem
    Nov 15 '18 at 15:53











  • you have 2 proposed answers below check them out and either will get you the non multiplied answers your looking for. @adamek339

    – junketsu
    Nov 15 '18 at 18:39













0












0








0








I have to tables



Customer



CustomerID Name Surname
---------------------------------
1 Adam Test
2 Robert Test2
3 John Test3


CustomerAddress



CustomerAddressId CustomerId AddressId
-------------------------------------------
1 1 1
2 1 2
3 1 3
4 2 6
5 2 7
6 2 8


I want to select CustomerId from these two tables.



I wrote this query but it multiplies my records.










share|improve this question
















I have to tables



Customer



CustomerID Name Surname
---------------------------------
1 Adam Test
2 Robert Test2
3 John Test3


CustomerAddress



CustomerAddressId CustomerId AddressId
-------------------------------------------
1 1 1
2 1 2
3 1 3
4 2 6
5 2 7
6 2 8


I want to select CustomerId from these two tables.



I wrote this query but it multiplies my records.







sql-server join






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 15 '18 at 16:48









marc_s

582k13011231269




582k13011231269










asked Nov 15 '18 at 15:38









adamek339adamek339

366




366







  • 1





    What query did you write? It would be helpful for us to have the query, expected output, etc.

    – Jeffrey Van Laethem
    Nov 15 '18 at 15:53











  • you have 2 proposed answers below check them out and either will get you the non multiplied answers your looking for. @adamek339

    – junketsu
    Nov 15 '18 at 18:39












  • 1





    What query did you write? It would be helpful for us to have the query, expected output, etc.

    – Jeffrey Van Laethem
    Nov 15 '18 at 15:53











  • you have 2 proposed answers below check them out and either will get you the non multiplied answers your looking for. @adamek339

    – junketsu
    Nov 15 '18 at 18:39







1




1





What query did you write? It would be helpful for us to have the query, expected output, etc.

– Jeffrey Van Laethem
Nov 15 '18 at 15:53





What query did you write? It would be helpful for us to have the query, expected output, etc.

– Jeffrey Van Laethem
Nov 15 '18 at 15:53













you have 2 proposed answers below check them out and either will get you the non multiplied answers your looking for. @adamek339

– junketsu
Nov 15 '18 at 18:39





you have 2 proposed answers below check them out and either will get you the non multiplied answers your looking for. @adamek339

– junketsu
Nov 15 '18 at 18:39












3 Answers
3






active

oldest

votes


















2














If you're looking for a distinct list of CustomerId from both tables, it'll be easiest to use UNION:



SELECT CustomerId
FROM Customer
UNION SELECT CustomerId
FROM CustomerAddress


Using UNION ALL would show duplicates in your result set.






share|improve this answer






























    1














    You can use DISTINCT keyword with JOIN :



    SELECT DISTINCT C.CustomerId 
    FROM Customer c INNER JOIIN
    CustomerAddress cs
    ON CD.CustomerId = C.CustomerId;


    Without DISTINCT it would return multiple CustomerIds as because of second table has multiple address for same customer.






    share|improve this answer






























      1














      If you want to capture Customers which has addresses you can use following script :



      SELECT * 
      FROM Customer C
      WHERE EXISTS (SELECT 1 FROM CustomerAddress CA WHERE C.CustomerId=CA.CustomerId)





      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%2f53322909%2fjoin-two-tables-in-sql-server%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









        2














        If you're looking for a distinct list of CustomerId from both tables, it'll be easiest to use UNION:



        SELECT CustomerId
        FROM Customer
        UNION SELECT CustomerId
        FROM CustomerAddress


        Using UNION ALL would show duplicates in your result set.






        share|improve this answer



























          2














          If you're looking for a distinct list of CustomerId from both tables, it'll be easiest to use UNION:



          SELECT CustomerId
          FROM Customer
          UNION SELECT CustomerId
          FROM CustomerAddress


          Using UNION ALL would show duplicates in your result set.






          share|improve this answer

























            2












            2








            2







            If you're looking for a distinct list of CustomerId from both tables, it'll be easiest to use UNION:



            SELECT CustomerId
            FROM Customer
            UNION SELECT CustomerId
            FROM CustomerAddress


            Using UNION ALL would show duplicates in your result set.






            share|improve this answer













            If you're looking for a distinct list of CustomerId from both tables, it'll be easiest to use UNION:



            SELECT CustomerId
            FROM Customer
            UNION SELECT CustomerId
            FROM CustomerAddress


            Using UNION ALL would show duplicates in your result set.







            share|improve this answer












            share|improve this answer



            share|improve this answer










            answered Nov 15 '18 at 15:52









            Jeffrey Van LaethemJeffrey Van Laethem

            2,05211526




            2,05211526























                1














                You can use DISTINCT keyword with JOIN :



                SELECT DISTINCT C.CustomerId 
                FROM Customer c INNER JOIIN
                CustomerAddress cs
                ON CD.CustomerId = C.CustomerId;


                Without DISTINCT it would return multiple CustomerIds as because of second table has multiple address for same customer.






                share|improve this answer



























                  1














                  You can use DISTINCT keyword with JOIN :



                  SELECT DISTINCT C.CustomerId 
                  FROM Customer c INNER JOIIN
                  CustomerAddress cs
                  ON CD.CustomerId = C.CustomerId;


                  Without DISTINCT it would return multiple CustomerIds as because of second table has multiple address for same customer.






                  share|improve this answer

























                    1












                    1








                    1







                    You can use DISTINCT keyword with JOIN :



                    SELECT DISTINCT C.CustomerId 
                    FROM Customer c INNER JOIIN
                    CustomerAddress cs
                    ON CD.CustomerId = C.CustomerId;


                    Without DISTINCT it would return multiple CustomerIds as because of second table has multiple address for same customer.






                    share|improve this answer













                    You can use DISTINCT keyword with JOIN :



                    SELECT DISTINCT C.CustomerId 
                    FROM Customer c INNER JOIIN
                    CustomerAddress cs
                    ON CD.CustomerId = C.CustomerId;


                    Without DISTINCT it would return multiple CustomerIds as because of second table has multiple address for same customer.







                    share|improve this answer












                    share|improve this answer



                    share|improve this answer










                    answered Nov 15 '18 at 15:41









                    Yogesh SharmaYogesh Sharma

                    33.8k51440




                    33.8k51440





















                        1














                        If you want to capture Customers which has addresses you can use following script :



                        SELECT * 
                        FROM Customer C
                        WHERE EXISTS (SELECT 1 FROM CustomerAddress CA WHERE C.CustomerId=CA.CustomerId)





                        share|improve this answer



























                          1














                          If you want to capture Customers which has addresses you can use following script :



                          SELECT * 
                          FROM Customer C
                          WHERE EXISTS (SELECT 1 FROM CustomerAddress CA WHERE C.CustomerId=CA.CustomerId)





                          share|improve this answer

























                            1












                            1








                            1







                            If you want to capture Customers which has addresses you can use following script :



                            SELECT * 
                            FROM Customer C
                            WHERE EXISTS (SELECT 1 FROM CustomerAddress CA WHERE C.CustomerId=CA.CustomerId)





                            share|improve this answer













                            If you want to capture Customers which has addresses you can use following script :



                            SELECT * 
                            FROM Customer C
                            WHERE EXISTS (SELECT 1 FROM CustomerAddress CA WHERE C.CustomerId=CA.CustomerId)






                            share|improve this answer












                            share|improve this answer



                            share|improve this answer










                            answered Nov 15 '18 at 15:50









                            Zeki GumusZeki Gumus

                            1,445313




                            1,445313



























                                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%2f53322909%2fjoin-two-tables-in-sql-server%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







                                這個網誌中的熱門文章

                                How to read a connectionString WITH PROVIDER in .NET Core?

                                Node.js Script on GitHub Pages or Amazon S3

                                Museum of Modern and Contemporary Art of Trento and Rovereto