Joining Tables, probably simple

[coopervegas]

Table 1 Table2
-------- --------
Ability Bonus Ability Bonus, 1, 2, 3
AC Bonus AC Bonus, 4,5,6

What I want the result to be
-----------------------------
Ability Bonus, 1
Ability Bonus, 2
Ability Bonus, 3
AC Bonus,4
AC Bonus,5
AC Bonus,6

I find figure out the right syntax to use, ive tried different combinations of JOIN and UNION but I can't seem to get a solution.

Any ideas out there? thanks.

 

[jedraw]

I'm not sure what the 1,2,3 ... represent. However, I think you may need a third table.
Here's an example that may be helpful

Table1 (BonusType with flds bonusId and bonusName)
This table deals only with bonus
===================
a -------Ability Bonus
b -------AC Bonus
c -------XYZ Bonus
etc

Table2 (Person with flds personId and personName)
This table deals only with person
1 ----John Smith
2 ----Bob Roberts
3 ----Sam Snead
etc.

Table3(BonusRecipient relating flds bonusId and personId)
The intent of this table is to identify which person received which bonus(s)
========
bonusId
personId
Lets say this table has values
a --- 2 meaning BonusType a was recieved by Person 2
c---- 3 meaning BonusType c was recieved by Person 3


BonusType Person
+-------- BonusRecipient------+

Select BonusType.bonusName, Person.personId from Bonus,
Person, BonusRecipient
where Bonus.bonusId = BonusRecipient.bonusId
and Person.personId = BonusRecipient.bonusId

would give as result
Ability Bonus 2
XYZ Bonus 3

If you change Person.personId to Person.personName in the SELECT line the result would be
Ability Bonus Bob Roberts
XYZ Bonus Sam Snead

Bonus.bonusId = BonusRecipient.bonusId represents a JOIN between Bonus and BonusRecipient (this is looking for values that match in these tables.)

Person.personId = BonusRecipient.personId represents a JOIN
between Person and BonusRecipient

 

[badley]

Is there any way of joinig them as above but keeing the join permanent.

So when we use 'explain' with a \G, the colum ref states that its linked to another table:

id : 1
select_type : SIMPLE
table : Product
type : ALL
possible_keys : NULL
Key : NULL
key_len : NULL
ref : NULL (I want to add a join here)
rows : 10
Extra :

 

[jedraw]

I do not use MySQL very much and don't know the details of EXPLAIN \G. I'm not familiar with "permanent join", but can see that you may be describing "constraints". You may wish to review <b>FOREIGN KEY Constraints, Primary and Unique keys</b>.

 

[badley]

Cheers jedraw. Yeah I've been looking at Foreign keys and stating the join within the query in php.