Help with Multiple Table Joins 1

[jsnuggs]

I'm fairly new with the mysql and database stuff so go easy on me I've been scanning web tutorial to try to find some example similar to this, but haven't had much luck. Anyway, I'm trying to do a sql query that will join several tables together.

Here's an example of the tables I have:

Table 1 (Main Table)
| Name | Type | Bonus1 | Bonus2 | Bonus3 |
------------------------------------------
| Abba |   1  |    2   |    1   |    3   |
| Baba |   2  |    3   |    2   |    1   |

Table 2 (Type Table)
| ID |   Name   |
-----------------
| 1  |   Test   |
| 2  |  Example |

Table 3 (Bonus Table)
| ID |  Name   |
----------------
| 1  |  Bronze |
| 2  |  Silver |
| 3  |  Gold   |

What I want to do is to join them together so I can say pull a row from Table1, but it will replace the Type # with the corresponding Type Name and each bonus # with the corresponding Bonus name.

So if I pulled line 1 the end results would be

| Name | Type | Bonus1 | Bonus2 | Bonus3 |
------------------------------------------
| Abba | Test | Silver | Bronze |  Gold  |

I can get so far as getting say Abba and Test to show up with something similar to this:

SELECT main.name type.name FROM main LEFT JOIN type ON (main.type = type.ID)

However I don't know how to go further than this. I especially don't understand how to do bonus1, bonus2 and bonus3 because they are each pulling from the same table just three times.

Thanks,
Jason

 

[sleipnir214]

Here's my recommendation...

Since the relationship between records in main and records in bonus is many-to-many, I recommend your removing the bonus records from main, and creating a new table which records the relationships.

Here's my version of main. I've added an ID column and removed all the bonus columns

+----+------+------+
| ID | name | type |
+----+------+------+
|  1 | Abba |    1 |
|  2 | Baba |    2 |
|  3 | Cala |    2 |
+----+------+------+

Here's a new table, main_to_bonus, which relates records in main to records in bonus:

+------+-------+
| main | bonus |
+------+-------+
|    1 |     2 |
|    1 |     1 |
|    1 |     3 |
|    2 |     3 |
|    2 |     2 |
|    2 |     1 |
+------+-------+

The data in main_to_bonus represents the same relationships as the data in your original main table. Notice there are no records relating the record in main for "cala" to any records in bonus.

Here's my query:
SELECT
m.name AS main_name, t.name AS type_name, b.name AS bonus_name
FROM
((main m LEFT JOIN type t ON m.type = t.id) LEFT JOIN main_to_bonus mb ON m.id = mb.main) LEFT JOIN bonus b ON mb.bonus = b.id
ORDER BY main_name, b.id

Which returns:

+-----------+-----------+------------+
| main_name | type_name | bonus_name |
+-----------+-----------+------------+
| Abba      | Test      | Bronze     |
| Abba      | Test      | Silver     |
| Abba      | Test      | Gold       |
| Baba      | Example   | Bronze     |
| Baba      | Example   | Silver     |
| Baba      | Example   | Gold       |
| Cala      | Example   | NULL       |
+-----------+-----------+------------+

______________________________________________________________________
TANSTAAFL!

 

[jsnuggs]

Heh had to read it a couple times before I really saw what you were doing, but now that I got it makes a lot of sense.

Thanks for the help,
Jason

 

[jsnuggs]

One other question on this. I tried what you suggested and did get it working, but I was wondering if there was a way to make it reeturn the results a little differently.

Right now the results are:

| Main_Name | Type_Name | Bonus_Name |
+------------------------------------+
| Abba      | Test      | Bronze     |
| Abba      | Test      | Silver     |
| Abba      | Test      | Gold       |
+-----------+-----------+------------+

Is there some way to make it display like this:

| Main_Name | Type_Name | Bonus  | Bonus  | Bonus  |
+--------------------------------------------------+
| Abba      | Test      | Bronze | Silver | Gold   |
+-----------+-----------+--------+--------+--------+

 

[sleipnir214]

I don't think there is.

It's a side-effect going down a related table with the bonuses, rather than across the main table.

Classically, what you do is in your programming language that drives your interface, scroll down the list collecting strings until main_name changes. Then present the bonuses in whatever way is best for your UI design.

______________________________________________________________________
TANSTAAFL!

 

[likelylad]

Maybe the following link might help you achieve what you want?????????

http://www.mysql.com/articles/wizard/index.html