CODEWITHSUNDEEP
mysqlsqlselectmariadb
pmoreira
pmoreira

Reputation: 113

One table multiple fields to other table

So I have a table with all user information and another table with product information.

On each product there is a reference to multiple users. e.g.: user1 is producer, user2 is productSupervisor and user3 is productManager.

How can I get information like this: product1.producer is User1.Name, product1.supervisor is user2.Name and product.manager is user3.Name

producer, productSupervisor and productManager are stored as userID.

Create Statements

CREATE TABLE employee (
  idEmployee int(11) NOT NULL AUTO_INCREMENT,
  networkID varchar(45) NOT NULL,
  firstName varchar(100) NOT NULL,
  lastName varchar(100) NOT NULL,
  phoneExtension varchar(10) DEFAULT NULL,
  email varchar(50) NOT NULL,
  mobile varchar(20) DEFAULT NULL,
  onCall tinyint(4) NOT NULL DEFAULT '0',
  lastLogin varchar(100) DEFAULT 'Never',
  active tinyint(4) NOT NULL DEFAULT '1',
  createDate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (idEmployee),
  KEY idx_employee (idTeam),
  KEY idx_employee_0 (idPosition),
  CONSTRAINT fk_employee_employeepositions FOREIGN KEY (idPosition) REFERENCES employeePositions (idPosition) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_employee_teams FOREIGN KEY (idTeam) REFERENCES teams (idTeam) ON UPDATE NO ACTION
)

CREATE TABLE product (
  idProduct int(11) NOT NULL,
  idSupervisor int(11) NOT NULL DEFAULT '1',
  idProducer int(11) NOT NULL DEFAULT '1',
  idManager int(11) NOT NULL DEFAULT '1',
  prodcutInfo longtext,
  PRIMARY KEY idProduct 
 )

Upvotes: 1

Views: 107

Answers (1)

Mureinik
Mureinik

Reputation: 312086

You can join the product table on the employee table thrice, once for each role:

SELECT idProduct, 
       CONCAT_WS(' ', s.firstname, s.lastname) AS supervisor,
       CONCAT_WS(' ', p.firstname, p.lastname) AS producer,
       CONCAT_WS(' ', m.firstname, m.lastname) AS manager
FROM   product pr
JOIN   employee s ON pr.idSupervisor = s.idEmployee
JOIN   employee p ON pr.idSupervisor = p.idEmployee
JOIN   employee m ON pr.idSupervisor = m.idEmployee

Upvotes: 1

Related Questions