CODEWITHSUNDEEP
mysqldatabase
Devin Rawlek
Devin Rawlek

Reputation: 127

MySQL - Reference data from one table to another table

I am trying to connect tables by listing an id from another table into a specific field in a row in another table.

This is what I'm trying to do;

Table Items

Id   Name    Price
 1   Drink   5
 2   Food    3

Table Character

Id   Name   Cash   Inventory 
 1   Foo    10     1
 2   Bar    10     2
 3   Stu    10     1, 2

I am trying to reference the Items so that when I pull data concerning the Character 'Stu' It pulls the data associated with Items '1 & 2' because he has them in his Inventory.

I've read up on Normalization and Foreign Keys but haven't been able to find a way to tie them together in the fashion I want. I've also found the INT field will not accept multiple comma separated integers.

Upvotes: 0

Views: 4419

Answers (2)

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

This should be easy using FIND_IN_SET looking at your table strructure here is the solution

SELECT 
    * 
FROM `characters` as c
LEFT JOIN items as i on FIND_IN_SET(i.id, c.Inventory)
WHERE c.name= 'Stu'

EDIT :

Here is the edited solution means one line solution.

SELECT 
    c.* ,
    group_concat(i.name) as ItemName,
    group_concat(i.price) as ItemPrices         
FROM `characters` as c
LEFT JOIN items as i on FIND_IN_SET(i.id, c.Inventory)
WHERE c.name= 'Stu'
group by c.name

If you are using php you can explode cell with php explode function and then loop through the cell to access all the cell values.

EDIT : 

SELECT 
    c.* ,
    group_concat(i.id) as ItemIds,
    group_concat(i.name) as ItemName,
    group_concat(i.price) as ItemPrices         
FROM `characters` as c
LEFT JOIN items as i on FIND_IN_SET(i.id, c.Inventory)
WHERE c.name= 'Stu'
group by c.name

Upvotes: 1

neo
neo

Reputation: 1298

You are trying to build one to many relationship. You need to place the foreign key into table items, i.e., CharacterId . Then you can pull data with JOIN.

Upvotes: 1

Related Questions