CODEWITHSUNDEEP
mysqljoin
user2956578
user2956578

Reputation: 71

Querying multiple mySQL tables in a single query

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:

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

I'd appreciate any help you can offer,

Upvotes: 0

Views: 40

Answers (1)

Rigel1121
Rigel1121

Reputation: 2016

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

Upvotes: 1

Related Questions