See the code below. I have stated remarks inbetween the code to explain the different actions.
CREATE TABLE users (
users_id VARCHAR(100) PRIMARY KEY
, username VARCHAR(100)
, password VARCHAR(100)
, designation VARCHAR(10)
)
CREATE TABLE travel_request (
request_id INT identity PRIMARY KEY
, users_id VARCHAR(100) FOREIGN KEY REFERENCES users(users_id)
, travel_purpose VARCHAR(100)
, tota_amount VARCHAR(10)
)
CREATE TABLE onward_journey (
onward_journey_id INT identity PRIMARY KEY
, request_id INT FOREIGN KEY REFERENCES travel_request(request_id)
, departuredate DATETIME
, from_place VARCHAR(10)
, to_place VARCHAR(10)
, no_of_days INT
)
-- insert initial data about the different users
insert into users values('0002','ram','ramki@123','progrmmer')
insert into users values('0006','ra','rai@123','Accounts')
--define the user by his/her name
declare @Username varchar(100)
set @Username = 'Ram'
--insert a new row in [travel_request] and use the users_id belonging to the user
insert into travel_request
select
Users_id-- select users_id from table [users] belonging to name given in the where clause
,'visit other branch' as travel_purpose
,'12000' as tota_amount
from users
where username = @Username-- select the name
--insert the travel specifications into table [onward_journey] and use the request_id from the latest request from the specified user
insert into onward_journey
select
max(request_id)-- select the latest request_id from table [travel_request] belonging to name given in the where clause
, '01-jul-2013' as departuredate
, 'chennai' as from_place
, 'banglore' as to_place
, 1 as no_of_days
from users
inner join travel_request
on users.users_id = travel_request.users_id
where username = @Username-- select the name
group by travel_request.users_id
--insert a second travel specifications into table [onward_journey] and use the request_id from the latest request from the specified user
insert into onward_journey
select
max(request_id)-- select the latest request_id from table [travel_request] belonging to name given in the where clause
, '02-jul-2013' as departuredate
, 'banglore' as from_place
, 'Hydrebad' as to_place
, 1 as no_of_days
from users
inner join travel_request
on users.users_id = travel_request.users_id
where username = @Username-- select the name
group by travel_request.users_id
--define the new user by his/her name
set @Username = 'Ra'
--insert a new row in [travel_request] and use the users_id belonging to the new user
insert into travel_request
select
Users_id-- select users_id from table [users] belonging to name given in the where clause
,'visit seminar' as travel_purpose
,'8000' as tota_amount
from users
where username = @Username-- select the name
--insert the travel specifications into table [onward_journey] and use the request_id from the latest request from the specified user
insert into onward_journey
select
max(request_id)-- select the latest request_id from table [travel_request] belonging to name given in the where clause
, '03-jul-2013' as departuredate
, 'chennai' as from_place
, 'delhi' as to_place
, 1 as no_of_days
from users
inner join travel_request
on users.users_id = travel_request.users_id
where username = @Username-- select the name
group by travel_request.users_id
-- show all inserted results by joining the three tables
select *
from users
inner join travel_request
on users.users_id = travel_request.users_id
inner join onward_journey
on travel_request.request_id = onward_journey.request_id