Access Values from a customer table to a room type table

I’m Stuck in the middle of my MySQL project. Basically for now I have three tables Room_Type, Room, Customer. Read my code for Room_Type Table and in Room Table, there’s a foreign key through which I can access its room_type (Standard, deluxe, etc) and in the customer table, there’s a foreign key thorough which I can access Room. But My Issue is I want to access it’s room_Type too through the customer Table and show it on the customer table. Can anyone here help me out. I’m really stuck here. Please correct me if I’m missing something, It would be a great help Thanks in Advance

Create Table Room_Type
(
rt_ID int not NULL,
room_type nvarchar(50) not NULL,
Primary key(rt_ID),

);


Create Table Room
(
room_ID int not NULL,
r_Capacity int not NULL,
room_floor int not NULL,
r_price int not NULL,
rt_ID int not NULL,
Primary key(room_ID),
Foreign Key(rt_ID) references Room_Type(rt_ID)
);

insert into Room_Type Values
(1,'Standard'),
(2,'Superior'),
(3,'Deluxe')

insert into Room Values
(1,2,0,5000,1),
(2,3,0,6000,2),
(3,1,0,3000,2)

select Room.room_ID,Room.r_capacity,Room.room_floor,Room.r_price ,Room_Type.room_type from Room  inner join Room_Type  ON Room.rt_ID = Room_Type.rt_ID
*/

/*Customer*/
/*
Create Table Customer 
(
c_ID int not NULL,
c_name nvarchar (20) not NULL,
c_ph nvarchar(11)not NULL,
c_cnic nvarchar (13) not NULL,
c_address nvarchar (100) not NULL,
room_ID int NULL,

Primary Key(c_ID),
Foreign Key(room_ID) references Room(room_ID)
)


insert INTO Customer (c_ID,c_name,c_PH,c_cnic,c_address) Values
(1,'Hamza','337492379','327623862','ABC123');
insert INTO Customer  Values
(2,'Saleem','347934723','34567890123','XYZ321',1)

Answer

I want to access it’s room_Type too through the customer Table and show it on the customer table

You can join twice:

select c.*, rt.room_type 
from customer c
inner join room r on r.room_id = c.room_id
inner join room_type rt on rt.rt_id = r.rt_id

Advertisement

Answer