I am designing a database for tracking files traveling among many offices. Each file movement must store office name from which file is received and office name to which file has been sent.

I have created a table called tbl_offices and populated with id & office_name pair.

I have created another table called tbl_file_movement with id as primary key and two columns named recv_from and sent_to. Both columns (recv_from and sent_to) store relevant ids of office from tbl_offices. Other columns store receiving date and sent date.

Is there anything wrong?

To get names of offices against the office id, I wrote a query as follows:

SELECT id, recv_from AS Recive_From_ID, sent_to AS Sent_To_ID
FROM tbl_file_movement
JOIN tbl_offices ON recv_from = tbl_offices.id

What should I add to SELECT statement to get office names from tbl_offices against ids?

CodePudding user response：

The way I understood your question, is that you want to replace the ID on the file movement table with the office name. I have written a query that will do such:

SELECT tbl_file_movement.id, (select office_name from tbl_offices where id = recv_from) AS Receive_From_Office_Name, 
(select office_name from tbl_offices where id = sent_to) AS Sent_To_Office_Name
FROM tbl_file_movement

SQL Fiddle

Edit updated link