Category: SQL Server

02 Mar 2017 22:05

Joined:	23/08/2014
Level:	Silver
Points:	104

Points:	5

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

03 Mar 2017 07:50

Joined:	28/04/2006
Level:	Gold
Points:	29773

Points:	4

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

06 Mar 2017 11:33

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	3

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

07 Mar 2017 16:42

Joined:	16/08/2007
Level:	Silver
Points:	248

Points:	4

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

07 Mar 2017 19:41

Joined:	30/04/2012
Level:	Gold
Points:	7233

Points:	3

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

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

About dotnetspider.com

Quick Links

General

Services