how to -- database question

morningstar

hey

i have a problem with the design of my database, i explain the situation:
i have 2 tables , table skills and table monsters

table skills is the main that has many rows table looks like this
id, name, monsterid, etc, etc...

table monsters is just a table so table 1 isnt getting amazing large looks like
id, monstername, etc, etc...

now in table skills i put in the column monsterid the id of the monster that has the skill, But some monsters have the same skills. (hope you still follow me)

how can i put in the record monsterid several id's of monsters and request them on my webpage as different.

sample:

1 - skill1 - 2;3 - etc..
2 - skill2 - 3 - etc..
3 - skill3 - 1;2 - etc..

1 - monster1 - etc..
2 - monster2 - etc..
3 - monster3 - etc

now i just used ; that sign as preview to show you what i mean but how should i put several id's of another table in 1 record?

greets morningstar

Roger_Ramjet

You dont. You have a table for Skills, a table for Monsters, and a 3rd table Monster_Skills that links the other 2. All it will contain will be MonsterID, SkillID and that is how you referance multiple skills for multiple monsters.

morningstar

hey

thanx roger, to bad it cant though.
hmm i never tryed to make a query between 3 tables..

SELECT S.id, S.name, M.id, M.monstername, MS.skillid, MS.monsterid
FROM skills S, monsters M, monsterskills MS
WHERE MS.skillid = S.id
AND MS.monsterid = M.id
AND MS.skillid = '1';

i get a query with all Monsters that have skill 1 this way?.. and doesnt this slow my connection down to select from 3 tables?

Roger_Ramjet

No, you cannot use the 3 tables like that - it's called a 'Chasm-Trap'. What you can do for a given monster id is get all the skill ids from 2 tables, or get all the monster ids for a given skill id. Getting further details about either requires further querying.

That is where the 'art' comes in. You modify the design according to your 'domain' knowledge - ie your knowledge of how the data will be used.

eg if you will normally want skill names for each monster then add them to the MS table

morningstar

ok thanx for the explanation 🙂

i will try some things and see what i can do with the 'art' and if it dont work out i just do

" if you will normally want skill names for each monster then add them to the MS table "

that 🙂

Roger_Ramjet

You can also use a sub-query to join skill name to id, and then join to the sub-query instead of the table in the outer query. Version dependant so read the manual about sub-queries and check your version

morningstar

hey

i read the manuall about Joins http://dev.mysql.com/doc/mysql/en/left-join-optimization.html that one..

but i dont really understand what a join excactly do..
if you have a join like this:

SELECT *
FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
WHERE b.key=d.key;

how are the tables excact connected to each other? i dont understand that really..

could you explain plz?

morningstar

forget my post above..
i just cant find out how to do it (also didnt found a manual with different versions about php about sub-querys and joins)
I already tryed hundred of things but just dont wanna work..

i got 3 tables now like:

skills

id -- name
1 -- skill1
2 -- skill2
3 -- skill3

monsters
id -- name
1 -- boss1
2 -- boss2
3 -- boss3

monsterskills

id -- skillid -- monsterid
1 -- 1 -- 2
2 -- 1 -- 3
3 -- 2 -- 1
4 -- 2 -- 3
5 -- 3 -- 1

now lets say i want to select skill1 with all the monster names that belong to it.. (boss2 and boss3 in this case)

how should the query look like? plz show it to me..

Roger_Ramjet

The full, unabridged query would be


SELECT monsters.monstername 
FROM monsters INNER JOIN monsterskills ON monster.id=monsterskills.monsterid 
WHERE monsterskills.skillid=1

That can be written in a short form with mysql, but untill you understand everything it is best to stick to the full and explicit notation so that you can see what is going on.

What it means is match (join) each and every monster to all it's corresponding entries in monsterskills, and filter the resulting rows for rows that have a skillid=1.

Try doing the query this way

SELECT * FROM monsters INNER JOIN monsterskills 
ON monsters.id=monsterskills.monsterid

and look at the results. Then it should be obvious what is going on.

morningstar

ok thanx roger,

i understand joins better now.. also read somethig about them.
still.. if i also want to select things out of the skills table at the same time, like skills.name .. then how should i do this?

better to do with a sub-query or is this also possible with JOIN command only
or should i just make a whole new query for this?

greets morningstar