How to Select from multiple tables in sql

 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.

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');

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);

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;

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;

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 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;