Displaying data from multiple tables on single form

[James Corden]

The database is to record new technologies/ideas/inventions developed in a large hospital. I am trying to put a new frontend on an existing database (the structure of which can't be changed).

I want to display information from multiple tables on a single page. Best explained with an example.

The Technology table is used to record information about the technology, such as:
TECHID - a unique number for each technology (not the primary key)
NAME - the name of the technology
MANAGERFK - the project manager for the technology - this is a numerical foreign key in the Technology table and links to the primary key of the User table
RCREATEDD - the date the technology was added to the database

The User table contains details of different types of users of the database, their permissions etc. and has:
CONTACTSFK - a numerical foreign key that links to the primary key of the Contacts table

The Contacts table contains personal details of all users (eg name, email address, job description etc)

I have created a Details page that displays the TechID, Name, Project Manager and Date for each technology when you click on the i that displays in the left hand side of the search results page. This currently displays the numerical value of MANAGERFK in the project manager field. I want to display the project managers actual name which means I have to use the value of MANAGERFK to query the User table to obtain the value of CONTACTSFK for that user, then query the Contacts table to obtain the persons actual name. How do I go about doing this so all the results display on a single page when you click the i in the search results page.

 

[James Corden]

In the hope of getting a response this is the SQL query I want to run on the Contacts table.
So currently my Details page would display MANAGERFK=1034 when displaying the details of a particular technology.
I want to use this number in the query below to display the actual person's name in the Details page
Can I do this in my existing Details page or do I have to set up a whole new View that extracts data from multiple tables?

SELECT FIRSTNAME, LASTNAME FROM CONTACTS WHERE PRIMARYKEY = (SELECT CONTACTSFK FROM USERS WHERE PRIMARYKEY = 1034)

The other option I have is just to add a new column to the Technology table (such as ManagerName) and add the actual Managers Name to this but I would rather not change the structure of the existing database.
Thanks

 

[larryk]

Use a view table. That way you keep your DB structure and just add a view. Dadabik will treat a View, the same as a table.

MySQL :: MySQL 8.0 Reference Manual :: 15.1.23 CREATE VIEW Statement

dev.mysql.com