Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

SQL Server Retrieve Data from Multiple Tables using Joins

Oct 12, 2012
Introduction:

In this article I will explain how to retrieve data from multiple tables in sql server.

Description:

In previous post I explained Difference b/w functions and stored procedures, Pass table as parameter in SQL Server 2008, differences between char, varchar and nvarchar, difference between tinyint,smallint,int,bigint and many articles relating to SQL Server. Now I will explain how to retrieve data from multiple tables in SQL Server.

If we want to retrieve data from multiple tables we need use Joins Concept in SQL Server.

Joins are used to get data from two or more tables based on relationship between some of the columns in tables for complete details check this article Joins Concept in SQL Server.

Before implement this example first design one table UserInformation in your database as shown below

Column Name
Data Type
Allow Nulls
UserId
Int (Set Identity=true)
No
UserName
varchar(50)
Yes
Location
Varchar(50)
Yes

After that design another table OrderDetails in your database like this

Column Name
Data Type
Allow Nulls
OrderId
Int (Set Identity=true)
No
OrderNo
Int
Yes
UserId
Int
No

After table design complete enter some data like as shown below

UserInfromation

OrderInformation

To get the data from these two tables here I am going to use Inner join concept in SQL Server

Inner Join

The join that displays only the rows that have a match in both the joined tables is known as inner join (Read more)

We need to write the query like as shown below to get data from two tables based on UserId match


SELECT u.UserId,u.UserName,u.Location,o.OrderNo FROM UserInformation u INNER JOIN OrderDetails o ON u.UserId=o.UserId
OutPut


If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

9 comments :

Anonymous said...

sir i tried the above code its working but when i ty to implement the same logic in in another table its showing error like invalid object name
what i tried is i have table where rollum and marks are stored and another table where rolnum and name is stored i tried the logic in this one it says "invalid object name" please help me am need of it...

Anonymous said...

sir please mail me the solution for the above query to my mail id konkasreekanth@gmail.com

Anonymous said...

check your table name and field name

Baiju EP said...

Dear suresh i have a problem in joining two tables the reason is
my first table persdata is having common field as pno (a single row in a particular person) and the second tables is also having pno(having muti entry for a person as this table is of qualification and a person may have more than 3 qualifications and their grading)
sample persdata(table)
pno name coy appt

sample qual(table)
pno qual grading recommendation

i tried my best to do it and not able to proceed

i want the output in a single row as in persdata to a gridview with all the qual combined in one coloumn and all the recommendations combined in one coloumn for a person. i am requesting for you help on this

Unknown said...
This comment has been removed by the author.
Unknown said...

sir, If all columns of two tables are different data types then it is possible to implement join ?

Anonymous said...

Very useful and valuable article provided by you.

बिष्णु पोख्रेल said...

sir how to update and add new value there.

Unknown said...

Hello Sir,
Does we have to make UserId as a foreign key?

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.