Select From Multiple Tables In SQL 



 



In this article, we are going to demonstrate how to select from multiple tables in SQL
 
SQL command in this statement is used to retrieve fields and records from multiple tables. we are need going to use a join query to get data from multiple tables. 

First, we are going to Create a Database and I named as sqlinner and change the master database to sqlinner database using the below commands.


Select from multiple tables in sql

create database sqlinner;



use sqlinner;


Let us we are taking three tables, two tables of brands named brand and brand price and the third table should be named as a stock table.

create table brand (brandId int  primary key identity(1,1) not null,name varchar(20) not null,
brand varchar(20) not null,Available varchar(20) not null);

select * from brand;



create table brandprice(brandId int primary key identity(1,1)not null,price varchar(20) not null);

select * from brandprice;


Insert data on brand Table


insert into brand (name,brand,Available)values('shirt','addias','yes');
insert into brand (name,brand,Available)values('shoes','woodland','yes');
insert into brand (name,brand,Available)values('shirt','woodland','No');
insert into brand (name,brand,Available)values('shoes','Bata','yes');
insert into brand (name,brand,Available)values('shirt','peter england','yes');
insert into brand (name,brand,Available)values('shirt','otto','yes');
insert into brand (name,brand,Available)values('shirt','weis','No');


Select from multiple tables in sql


Insert data on BrandPrice Table

insert into brandprice(price)values(500);
insert into brandprice(price)values(1500);
insert into brandprice(price)values(200);
insert into brandprice(price)values(700);
insert into brandprice(price)values(200);
insert into brandprice(price)values(500);
insert into brandprice(price)values(2500);


Select from multiple tables in sql

Here, we use left join to connect the two table brand table and brandprice table on both tables using primary key ID's


select brand.brandId , brand.name,brand.brand,brandprice.price from brand 
left join brandprice on 
brand.brandId=brandprice.brandId;


Select from multiple tables in sql


create table stock(brandId int identity(1,1) primary key,stock_no varchar(20) not null,
stock_bal varchar(20) not null);

select * from stock;




Insert Data on Stock Table


insert into stock(stock_no ,stock_bal)values(101,54);
insert into stock(stock_no ,stock_bal)values(354,2);
insert into stock(stock_no ,stock_bal)values(256,112);
insert into stock(stock_no ,stock_bal)values(87,10);
insert into stock(stock_no ,stock_bal)values(135,42);
insert into stock(stock_no ,stock_bal)values(856,22);
insert into stock(stock_no ,stock_bal)values(1023,17);



alter table stock add brand varchar(20);

update stock set brand = 'addias' where brandId=1;
update stock set brand = 'woodland' where brandId=2;
update stock set brand = 'bata' where brandId=3;
update stock set brand = 'peter england' where brandId=4;
update stock set brand = 'weis' where brandId=5;
update stock set brand = 'walkroo' where brandId=6;



alter table brandprice add stock_no varchar(20);

update brandprice set stock_no =101 where brandId=1;
update brandprice set stock_no =354 where brandId=2;
update brandprice set stock_no =256 where brandId=3;
update brandprice set stock_no =87 where brandId=4;
update brandprice set stock_no =135 where brandId=5;
update brandprice set stock_no =856 where brandId=6;
update brandprice set stock_no =1023 where brandId=7;


Select from multiple tables in sql

Here, we use Inner join to connect the two table brand table and brandprice table on both tables using primary key IDs and using where clause to which row having yes value to retrieve from "available" column.


select b.brandId,b.brand,b.name,bp.price from brand as b 
inner join brandprice as bp on 
b.brandId = bp.brandId where Available='yes' ;


Select from multiple tables in sql

select b.brandId,b.brand,b.name,bp.price,s.stock_no from brand as b 
inner join brandprice as bp on 
b.brandId= bp.brandId 
inner join stock s on 
s.stock_no =bp.stock_no;


Select from multiple tables in sql
Previous Post Next Post