You must Sign In to post a response.
  • Category: SQL Server

    Using two table write the query for inner join and full join

    Customer table as follows


    Custid Firstname Lastname email address city

    1 Ram Kumar ramkumar@gmail.com VernonHwy Chennai
    2 Rajesh Kannan rajesh@gmail.com Hancock Chennai
    3 Thomas Jaffer thomas@gmail.com Constitution Chennai
    4 Paul Adams paul@gmail.com James Chennai
    5 James Maddison maddison@gmail.com Hancock Chennai


    Order table as follows

    Orderid Orderdate Amount Customerid

    1 07/04/2014 234 1
    2 3/14/2015 79 3
    3 05/23/2016 124 2
    4 09/03/2017 66 3


    From using above customer and order table i want to write the query for INNER JOIN and FULL JOIN and Get the output.


    for that how can i write the query.
  • #768984
    Hai Rao,
    For the inner join, you just need the query like below:

    Select C.Custid, C.Firstname, C.Lastname, C.email, C.address, C.city,
    SUM(O.Amount) as Total
    From Customer C INNER JOIN Order O
    ON C.Custid = O.Orderid

    For the Full Join, you need to have the Left Outer Join and then Right Outer Join and Union them to get the results from Full Join.
    Also the other way is to use FULL OUTER JOIN straight away:

    Select C.Custid, C.Firstname, C.Lastname, C.email, C.address, C.city,
    SUM(O.Amount) as Total
    From Customer C
    FULL OUTER JOIN Order O
    ON C.Custid = O.Orderid

    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #769000
    Hi,

    What is your query?

    What do you want using those 2 tables?

    What is the output you are expecting using those tables?

    for simply you want to know the output using INNER JOIN & FULLOUTERJOIN then you can refer above query or Google it.

    but if you want the specified result like, you want common data in both the tables you can use INNER JOIN, if you want to return the full or both tables data if there is no matching then you can use FULL OUTER JOIN.

    before doing this refer few articles in online then start to implement.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #769012
    Inner Join Query
    Select C.Custid, C.Firstname, C.Lastname, C.email, C.address, C.city,
    SUM(O.Amount) as Total
    From Customer C INNER JOIN Order O
    ON C.Custid = O.Customerid
    GROUP BY C.Custid, C.Firstname, C.Lastname, C.email, C.address, C.city


    In the case of inner join we dont get the data of customer id 4 and 5 because that customer id is not having the details in order table.
    CustId Firstname Lastname Email Address City Total
    1 Ram Kumar ramkumar@gmail.com VernonHwy Chennai 234
    2 Rajesh Kannan rajesh@gmail.com Hancock Chennai 124
    3 Thomas Jaffer thomas@gmail.com Constitution Chennai 145(79+66)

    Outer Join Query
    Select C.Custid, C.Firstname, C.Lastname, C.email, C.address, C.city,
    SUM(O.Amount) as Total
    From Customer C OUTER JOIN Order O
    ON C.Custid = O.Customerid
    GROUP BY C.Custid, C.Firstname, C.Lastname, C.email, C.address, C.city


    The result is going to be
    CustId Firstname Lastname Email Address City Total
    1 Ram Kumar ramkumar@gmail.com VernonHwy Chennai 234
    2 Rajesh Kannan rajesh@gmail.com Hancock Chennai 124
    3 Thomas Jaffer thomas@gmail.com Constitution Chennai 145
    4 Paul Adams paul@gmail.com James Chennai
    5 James Maddison maddison@gmail.com Hancock Chennai

  • #769013
    You can use given syntax of Join in SQL for two table write the query for inner join and full join

    Inner Join Syntax is,
    SELECT column-name-list
    from table-name1
    INNER JOIN
    table-name2
    WHERE table-name1.column-name = table-name2.column-name

    Full Outer Join Syntax is,

    select column-name-list
    from table-name1
    FULL OUTER JOIN
    table-name2
    on table-name1.column-name = table-name2.column-name


Sign In to post your comments