Querying multiple mySQL tables in a single query

Question

I need to write a query for a scout database that compares the requirements of a badge with the skills a given member has already earned. The purpose being that several skills are applicable to multiple badges. My relevant tables (there are many) look like this:

Badge_Table: Badge_ID, Badge_Name, Badge_Description,

Badge_Skills_Table: Badge_Skill_ID, Badge_ID, Skill_ID,

Skills_Table: Skill_ID, Skill_Name, Skill_Description,

Skills_Earned_Table: Skills_Earned_ID Skill_ID User_ID

User_Table: User_ID, Name, Age, Address

Primary keys are shown in italics, and the foreign key relationships go from Badge_table to Badge_Skills_Table to Skills_Table to Skills_Earned_table to User_Table.

So far I have came up with the following ideas:

Selects all badges for named skill

SELECT badge_table.badge_name
FROM (badge_table 
     INNER JOIN badge_skills_table ON badge_ID 
     INNER JOIN Skills_Table ON skill_Id)
WHERE Skills_Table.Skill_Id = 1;

Selects all badges for each skill

SELECT badge_table.badge_name
FROM (badge_table 
      INNER JOIN badge_skills_table ON badge_ID 
      INNER JOIN Skills_Table ON skill_Id)
WHERE Skills_Table.Skill_Id = Skill_Badge_Table.Skill_Id

Selects all badges for named skill for named User - not quite working

SELECT badge_table.badge_name
FROM (badge_table 
     INNER JOIN badge_skills_table ON badge_ID 
     INNER JOIN Skills_Table ON skill_Id 
     INNER JOIN Skills_Earned_Table On skill_ID 
     INNER JOIN users_table ON user_ID)
WHERE Skills_Earned_Table.User_ID= 1 AND Skills_Earned_Table.SKILL_ID = Skill_Badge_Table.skill_ID

So can anyone help guide me with the following:

How to return all badges that a given skill is applicable for. (Done)
How to return all badges that a given scout has earned skills towards.
To return all badges the a given scout has earned all the skills for.

I'd appreciate any help you can offer,

All your ON clauses are wrong. They need to be a comparison, e.g. ON badge_skills_table.badge_ID = badge_table.badge_id. — Barmar
– Barmar, Commented Feb 12, 2015 at 2:15
There's no need to put parenthese around all the INNER JOIN clauses. — Barmar
– Barmar, Commented Feb 12, 2015 at 2:16

Rigel1121 · Accepted Answer · 2015-02-12 05:36:15Z

1

You have no <conditions> in your ON clause. Try my query below:

SELECT A.badge_name
FROM badge_table A
     INNER JOIN badge_skills_table B ON A.badge_ID=B.badge_ID
     INNER JOIN Skills_Table C ON B.skill_Id=C.skill_ID
     INNER JOIN Skills_Earned_Table D ON C.skill_ID=D.skill_ID 
     INNER JOIN users_table E ON user_ID ON D.user_ID=E.user_ID
WHERE D.User_ID= 1 AND D.skill_ID = B.skill_ID

answered Feb 12, 2015 at 5:36

Rigel1121

2,0171 gold badge19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David K-J Over a year ago

Assuming the columns strong the same data type, OP could have used USING instead of ON. This would join the table on the specified column which is present in both tables.

Collectives™ on Stack Overflow

Querying multiple mySQL tables in a single query

Selects all badges for named skill

Selects all badges for each skill

Selects all badges for named skill for named User - not quite working

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Selects all badges for named skill

Selects all badges for each skill

Selects all badges for named skill for named User - not quite working

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related