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