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 Email
|
|||
|
|
4 comments :
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...
sir please mail me the solution for the above query to my mail id konkasreekanth@gmail.com
check your table name and field name
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