Fetching data from two or more tables(Joining of tables)

Selecting data from two or more tables(Joining of tables)

Hello Friends, today we will learn how to select data(records) from more than one table. For this purpose we will use joining of tables. Joining simply join one or two or more than two tables by some conditions as per need. One there is a joining between one table means one table is joined with self it is called self joining.

I will here consider a general example for joining which will be used mostly.

Create two tables as tbl_userlogin and tbl_userdetails as below:

Design view of tbl_userlogin:

create table  tbl_userlogin
create table tbl_userlogin

Query:

CREATE TABLE [dbo].[tbl_userlogin](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[username] [varchar](50) NULL,
[password] [varchar](50) NULL
)

Design view of tbl_userdetails

create table tbl_userdetails
create table tbl_userdetails

 

Query:

CREATE TABLE [dbo].[tbl_userdetails](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[username] [varbinary](50) NOT NULL,
[firstname] [varchar](50) NULL,
[lastname] [varchar](50) NULL,
[emailid] [varchar](50) NULL,
[phone] [varchar](50) NULL,
[address] [varchar](300) NULL
)

Now insert some records into both tables.

insert into dbo.tbl_userlogin(username,password) values(‘vipin’,’mishra’)
insert into dbo.tbl_userlogin(username,password) values(‘user1′,’pass1’)

insert into dbo.tbl_userdetails(username,firstname,lastname,emailid,phone,address)
values(‘vipin’,’vipin’,’mishra’,’vipin@pjsindia.com’,’8981881162′,’kolkata’)
insert into dbo.tbl_userdetails(username,firstname,lastname,emailid,phone,address)
values(‘user1′,’user’,’name’,’support@pjsindia.com’,’8013985540′,’varanasi’)

Now we will fetch records from both tables. See the query:

select u.username,d.firstname,d.lastname,d.emailid,d.phone,d.address from dbo.tbl_userlogin u
join tbl_userdetails d on u.username=d.username

This will return all the records where the joining condition will full fill. Here whenever there is same username in both tables the record will be fetched.

Now see the next example:

select u.username,d.firstname,d.lastname,d.emailid,d.phone,d.address from dbo.tbl_userlogin u
join tbl_userdetails d on u.username=d.username where u.username=’vipin’

This will return records only having username=’vipin’ in both tables.

Enjoy programming….

 

 

 

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: