Multiple SQL Queries with INNER JOIN different parameters?

user_7066880 0
Viewed 271

I'm not sure what the terminology is for this but I have a mysql query

SELECT name, invTypes.typeName, character_skills.trained_skill_level FROM character_skills INNER JOIN character_infos ON character_infos.character_id = character_skills.character_id INNER JOIN invTypes ON character_skills.skill_id = invTypes.typeID WHERE character_skills.active_skill_level <5 AND skill_id = 12203 AND name = 'Mokey PYD';

However, there are 2 variables that change and I need to show all of this on a single table.

Those two variables are 

WHERE character_skills.active_skill_level <5 AND skill_id = 12203

For instance

I need to see if the value of active_skill_level is < 5 for skill_id 12203 BUT need to also check if active_skill_level < 4 for skill_id 12204 and I need these to show on the same table...

Can someone help me out a little bit? Much Appreciated.

inner-join select mysql
2 Answers

Use UNION ALL

SELECT name, invTypes.typeName, character_skills.trained_skill_level 
FROM character_skills INNER JOIN character_infos ON character_infos.character_id = character_skills.character_id 
INNER JOIN invTypes ON character_skills.skill_id = invTypes.typeID 
WHERE character_skills.active_skill_level <5 AND skill_id = 12203 AND name = 'Mokey PYD'
union all
SELECT name, invTypes.typeName, character_skills.trained_skill_level 
FROM character_skills INNER JOIN character_infos ON character_infos.character_id = character_skills.character_id 
INNER JOIN invTypes ON character_skills.skill_id = invTypes.typeID 
WHERE character_skills.active_skill_level <4 AND skill_id = 12204 AND name = 'Mokey PYD'
user_69148640

An alternative to union all is an OR clause 

SELECT name, invTypes.typeName
  , character_skills.trained_skill_level 
FROM character_skills 
INNER JOIN character_infos ON character_infos.character_id = character_skills.character_id 
INNER JOIN invTypes ON character_skills.skill_id = invTypes.typeID 
WHERE ( character_skills.active_skill_level <5 
AND skill_id = 12203  )  
OR  ( character_skills.active_skill_level <4 
AND skill_id = 12204  ) 
AND name = 'Mokey PYD';
user_35223120
Related