mySQL: How to select specific columns in a three-table join vs using *?-CodePudding

I'm creating a volunteer skills management scenario, similar to a students/classes/grades matrix.

I have three tables thus:

table1: skill_categories (28 rows)
fields:
skill_id (int,pk)
skill (varchar)

table2: volunteers (111 rows)
fields:
vol_id (int,pk)
full_name (varchar)

table3: skill_assessments (3108 rows)
fields:
id  (int,pk)
skill_id (int)
vol_id (int)
ranking (int)

I want to see every skill from t1, the full_name from t2 of everyone that has a ranking greater than zero and lastly the ranking and the id from t3. The last item, skill_assessments.id would be used for updates.

But all 3 of these tables have other columns that I'd like to remove from my query result. For instance the volunteers table has 11 columns.

If I do a simple select query on t3 "where ranking > 0", I get a query result of 1180 rows (out of the 3108) and this is confirmed in the result of my JOIN statement below.

All this to frame the question: how to select specific columns in a three-table join?

I get the exact rows I need from this query, but I want to remove a lot of columns:

SELECT * 
from skill_categories 
LEFT JOIN skill_assessments ON skill_assessments.skill_id = skill_categories.skill_id 
LEFT JOIN volunteers ON volunteers.vol_id = skill_assessments.vol_id 
WHERE skill_assessments.ranking > 0 
ORDER BY skill_categories.skill ASC, skill_assessments.ranking DESC
;

CodePudding user response：

Start from the basic.

Select only the columns that you really need

I want to see every skill from t1

You need to select every skill row from table1 , a left join is needed.

the full_name from t2 of everyone that has a ranking greater than zero and lastly the ranking and the id from t3

Here you need to inner join table2 with table3 using the condition where table3.ranking > 0

The final query:

select sc.skill,
       v.full_name,
       sa.id,
       sa.ranking
from skill_categories sc
left join skill_assessments sa on sc.skill_id=sa.skill_id 
inner join volunteers v on v.vol_id=sa.vol_id
where sa.ranking > 0;