user user - 3 months ago 8
MySQL Question

how to assign multiple values from to a value from another table in php/mysql

this is my requirement_tbl

and my documents_tbl

and my expected result

-------------------------------------------------------------
| doc_id | document_name | requirement_id |requirement_name |
-------------------------------------------------------------
| 1 |Certifications | 3 | requirementname |
| | | 1 | requirementname |
| | | 7 | requirementname |
| | | 9 | requirementname |
| 2 |CAV | 1 | requirementname |
| | | 4 | requirementname |
| | | 3 | requirementname |
| | | 5 | requirementname |
-------------------------------------------------------------


Is this output possible in displaying in HTML using PHP? Someone can show me how to?
Is this related to foreign keys and joining tables?
Thanks in advance.

Cam Cam
Answer

It can be helpful to construct a sqlfiddle for questions such as these so that reviewers can follow along with a common schema/dataset. Given the following schema:

CREATE TABLE `document` (
  `document_id` INT(11) PRIMARY KEY AUTO_INCREMENT,
  `document_name` VARCHAR(64) DEFAULT NULL,
  `document_amount` INT(4),
  `requirement1` INT(11) DEFAULT 0,
  `requirement2` INT(11) DEFAULT 0,
  `requirement3` INT(11) DEFAULT 0,
  `requirement4` INT(11) DEFAULT 0,
  `requirement5` INT(11) DEFAULT 0,
  `requirement6` INT(11) DEFAULT 0,
  `requirement7` INT(11) DEFAULT 0
);

CREATE TABLE `requirement` (
  `requirement_id` INT(11) PRIMARY KEY AUTO_INCREMENT,
  `document_id` INT(11) DEFAULT 0,
  `requirement_name` VARCHAR(64) DEFAULT NULL
);

The main obstacle is that the requirements for each document are non-normalized; there may be as many as 7 different requirement options spread out across each of the document columns. To allow for that, you'll need to bring them together into such a way that they represent a single column, which can be done via union:

SELECT `document_id`, `requirement1` as 'requirement' FROM `document`
UNION
SELECT `document_id`, `requirement2` as 'requirement' FROM `document`
UNION
SELECT `document_id`, `requirement3` as 'requirement' FROM `document`
UNION
SELECT `document_id`, `requirement4` as 'requirement' FROM `document`
UNION
SELECT `document_id`, `requirement5` as 'requirement' FROM `document`
UNION
SELECT `document_id`, `requirement6` as 'requirement' FROM `document`
UNION
SELECT `document_id`, `requirement7` as 'requirement' FROM `document`

Then you're able to join together your query into the desired result set:

SELECT d.document_id, d.document_name, r.requirement_id, r.requirement_name FROM
`document` as `d` JOIN
(
# correct for non-normalization via UNION on requirements
SELECT `document_id`, `requirement1` as 'requirement' FROM `document`
UNION
SELECT `document_id`, `requirement2` as 'requirement' FROM `document`
UNION
SELECT `document_id`, `requirement3` as 'requirement' FROM `document`
UNION
SELECT `document_id`, `requirement4` as 'requirement' FROM `document`
UNION
SELECT `document_id`, `requirement5` as 'requirement' FROM `document`
UNION
SELECT `document_id`, `requirement6` as 'requirement' FROM `document`
UNION
SELECT `document_id`, `requirement7` as 'requirement' FROM `document`
) `req`
ON d.document_id = req.document_id
JOIN `requirement` as `r`
ON req.requirement = r.requirement_id
WHERE req.requirement > 0
ORDER BY d.document_id, r.requirement_id;
Comments