CODEWITHSUNDEEP
mysql
Rae Kun
Rae Kun

Reputation: 13

how to get value from another table by their id's from another table?

Documents table (req1-7 are id's of requirements from another table)

| document_id | document_name | document_amount | req_1 | req_2 | req_3 | req_4 | req_5 | req_6 | req_7 |
|      1      |  Diploma      |      40         |   1   |  3    |    4  |     4 |    6  |  7    |  8    |

Requirements table

| requirement_id | requirement_name |
|    1           | 1 x 1 picture    |
|    2           | 2 x 2 picture    |
|    3           | Registration form|
|    4           | Clearance        |
|    5           | Medical cert     |
|    6           | xray result      |
|    7           | excuse letter    |
|    8           | affidavit        |
|    9           | comsoc clearance |

expected result is similar to the documents table but the the requirements names a displayed with the corresponding value of their id's .

I want to know the correct syntax to query those

So far my query is:

SELECT * FROM document_tbl 
            WHERE requirement1,requirement2,requirement3,requirement4,requirement5,requirement6,requirement7
            IN (
                SELECT requirement_name FROM requirements_tbl WHERE requirement_id=requirement1,requirement2,requirement3,requirement4,requirement5,requirement6,requirement7 )";

But I screwedup somehow. Thanks in advance for the help. I would appreciate it.

Upvotes: 0

Views: 100

Answers (2)

Martynas Tumas
Martynas Tumas

Reputation: 256

Something like this:

SELECT d.document_id,
   d.document_name,
   d.document_amount,
   r1.requirement_name AS req_1,
   r2.requirement_name AS req_2,
   r3.requirement_name AS req_3,
   r4.requirement_name AS req_4,
   r5.requirement_name AS req_5,
   r6.requirement_name AS req_6,
   r7.requirement_name AS req_7
FROM documents    AS d,
   requirements AS r1,
   requirements AS r2,
   requirements AS r3,
   requirements AS r4,
   requirements AS r5,
   requirements AS r6,
   requirements AS r7
WHERE d.req_1 = r1.requirement_id
   AND d.req_2 = r2.requirement_id
   AND d.req_3 = r3.requirement_id
   AND d.req_4 = r4.requirement_id
   AND d.req_5 = r5.requirement_id
   AND d.req_6 = r6.requirement_id
   AND d.req_7 = r7.requirement_id

and so on.

Upvotes: 0

Mohamed Yasin
Mohamed Yasin

Reputation: 440

Intermediate table (document_requirements)

| document_id | requirement_id |
|      1      |  1             |
|      1      |  3             |
|      1      |  4             |
|      1      |  6             |
|      1      |  6             |
|      1      |  7             |
|      1      |  8             |

Query

SELECT d.document_id,dr.requirement_id,r.requirement_name
FROM documents AS d
JOIN document_requirements AS dr ON (dr.document_id=d.document_id)
JOIN requirements AS r ON (r.requirement_id=dr.requirement_id)
WHERE d.document_id = 1;

Upvotes: 1

Related Questions