CODEWITHSUNDEEP
sql
Bogdan Galan
Bogdan Galan

Reputation: 65

SQL multiple columns that has one column as relation

I have a table named engrams with columns 

mat_name1, mat_name2, mat_name3, mat_name4

that has a relation with table resources on column id.

I have this SQL statement

SELECT * 
FROM engrams 
INNER JOIN resources ON engrams.mat_name1 = resources.id

How do I add the other mat_name columns with resources.id and how do I echo the column res_name from resources table for every mat_name?

engrams table

resources table

Upvotes: 1

Views: 717

Answers (2)

Nick
Nick

Reputation: 147166

You need to JOIN your engrams table to the resources table 4 times, once for each material name. Since you will have similar column names from each JOIN of the resources table, you will probably need to use aliases to distinguish them in your application framework:

SELECT e.name,
       e.description,
       e.mat_name1, r1.res_name AS res_name1, r1.description AS res_descr1,
       e.mat_name2, r2.res_name AS res_name2, r2.description AS res_descr2,
       e.mat_name3, r3.res_name AS res_name3, r3.description AS res_descr3,
       e.mat_name4, r4.res_name AS res_name4, r4.description AS res_descr4
FROM engrams e
LEFT JOIN resources r1 ON r1.id = e.mat_name1
LEFT JOIN resources r2 ON r2.id = e.mat_name2
LEFT JOIN resources r3 ON r3.id = e.mat_name3
LEFT JOIN resources r4 ON r4.id = e.mat_name4

Upvotes: 2

Ben
Ben

Reputation: 1346

If I understand your question correctly, then all you need to do is add OR statements to your ON clause.

SELECT * 
FROM engrams 
INNER JOIN resources 
ON engrams.mat_name1 = resources.id
    or engrams.mat_name2 = resources.id
    or engrams.mat_name3 = resources.id
    or engrams.mat_name4 = resources.id

As is that query will return all values for both engrames and resources. If you don't want everything, just explicity define which you do such as:

SELECT resources.res_name, engrams.mat_name1, engrams.mat_name2, engrams.mat_name3,engrams.mat_name4
FROM engrams 
INNER JOIN resources 
ON engrams.mat_name1 = resources.id
    or engrams.mat_name2 = resources.id
    or engrams.mat_name3 = resources.id
    or engrams.mat_name4 = resources.id

If "echo the column" means something else, you'll have to include some example output or something.

Upvotes: 1

Related Questions