I have three tables, monsters
items
and joinedInfo
I want to join monsters
and items
so that joinedInfo shows what items can be dropped by what monsters.
Example:
Monsters:
ID | MonsterName |
---|---|
1 | Cow |
2 | Chicken |
3 | Goblin |
Items:
ID | ItemName | MonsterForeignKey |
---|---|---|
1 | Bones | 1,2,3 |
2 | Feathers | 2 |
3 | Gold | 3 |
4 | Eggs | 2 |
5 | Cow hide | 1 |
6 | Dagger | 3 |
JoinedInfo:
ID | MonsterName | ItemName |
---|---|---|
1 | Goblin | Bones |
2 | Goblin | Gold |
3 | Goblin | Dagger |
4 | Cow | Bones |
5 | Cow | Cow hide |
6 | Chicken | Bones |
7 | Chicken | Feather |
8 | Chicken | Eggs |
Some items are unique to one monster and other items are shared with multiple monsters.
-
Cow, chicken and goblin can drop bones.
-
Chicken and goblin can drop coins.
-
Only cow can drop cow hide.
-
Only goblin can drop sword.
-
Only chicken can drop eggs.
How do I associate different items to to the different monsters? Eventually there will be a lot more monsters and items right but now I just want to figure out how to associate them together.
An example I found on google I tweaked to fit what I thought would join them:
SELECT items.ID, items.ItemName, monsters.MonsterName, monsters.ID FROM items, monsters;
LEFT OUTER JOIN joinedInfo ON MonsterName.ID = joinedInfo.ID AND MonsterName.ID = items.MonsterForeignKey
LEFT OUTER JOIN groups ON group_elements.GroupID = groups.ID
I was hoping for something like this
JoinedInfo:
ID | MonsterName | ItemName |
---|---|---|
1 | Goblin | Bones |
2 | Goblin | Gold |
3 | Goblin | Dagger |
4 | Cow | Bones |
5 | Cow | Cow hide |
6 | Chicken | Bones |
7 | Chicken | Feather |
8 | Chicken | Eggs |
What I get on sqlfiddle:
MySQL 5.6 Schema Setup:
CREATE TABLE monsters
(`ID` int, `MonsterName` varchar(43))
;
INSERT INTO monsters
(`ID`, `MonsterName`)
VALUES
(1, 'Cow'),
(2, 'Chicken'),
(3, 'Goblin')
;
CREATE TABLE items
(`ID` int, `ItemName` varchar(9), `MonsterForeignKey` int)
;
INSERT INTO items
(`ID`, `ItemName`, `MonsterForeignKey`)
VALUES
(1, 'Gold', 3),
(2, 'Bones', 1),
(3, 'Feathers', 2),
(4, 'Cow Hide', 1),
(5, 'Dagger', 3),
(6, 'Eggs', 2)
;
CREATE TABLE group_elements
(`GroupID` int, `ElementID` int)
;
INSERT INTO group_elements
(`GroupID`, `ElementID`)
VALUES
(3, 1),
(1, 2),
(2, 2),
(2, 3),
(3, 3)
;
Query 1:
SELECT items.ID, items.ItemName, monsters.MonsterName FROM items, monsters
| ID | ItemName | MonsterName |
|----|----------|-------------|
| 1 | Gold | Cow |
| 1 | Gold | Chicken |
| 1 | Gold | Goblin |
| 2 | Bones | Cow |
| 2 | Bones | Chicken |
| 2 | Bones | Goblin |
| 3 | Feathers | Cow |
| 3 | Feathers | Chicken |
| 3 | Feathers | Goblin |
| 4 | Cow Hide | Cow |
| 4 | Cow Hide | Chicken |
| 4 | Cow Hide | Goblin |
| 5 | Dagger | Cow |
| 5 | Dagger | Chicken |
| 5 | Dagger | Goblin |
| 6 | Eggs | Cow |
| 6 | Eggs | Chicken |
| 6 | Eggs | Goblin |
Query 2:
LEFT OUTER JOIN joinedInfo ON monsterName.ID = joinedInfo.ID AND monsterName.ID = items.MonsterForeignKey
LEFT OUTER JOIN groups ON group_elements.GroupID = groups.ID
2
Answers
With ID representing the name of the animal and MonsterForeignKey representing the item that can be dropped by that animal, an INNER JOIN to join the two tables by linking
t1.ID=t2.MonsterForeignKey
returns the following:By using INNER JOIN, we are filtering so as to include the ID, Monster Name and the Item Name that can be dropped by the animal.
Since MonsterForeignKey contains this information, this is why we need to link this variable to the ID.
You should be using the
FIND_IN_SET
for getting your answer,try this