Question 203416 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How do I match items from one table to other items in a 2nd table in SQL?

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:

SQL Fiddle

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

Results:

| 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

Results:

Tags:

2

Answers


  1. 0

    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:

    select t1.ID, t1.MonsterName, t2.ItemName from monsters as t1
inner join Items as t2 on t1.ID=t2.MonsterForeignKey order by id;

 id | monstername | itemname 
----+-------------+----------
  1 | Cow         | Bones
  1 | Cow         | Cow Hide
  2 | Chicken     | Feathers
  2 | Chicken     | Eggs
  3 | Goblin      | Gold
  3 | Goblin      | Dagger
(6 rows)

    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.

    Login or Signup to reply.
  2. 0

    You should be using the FIND_IN_SET for getting your answer,
    try this

    SELECT m.`ID` AS monsterId, 
m.`MonsterName`, 
i.`ItemName` 
 FROM `monsters` m 
 JOIN items i 
 ON FIND_IN_SET(m.`ID`,i.`MonsterForeignKey`)
 ORDER BY m.`ID`
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search