Tilted Forum Project Discussion Community - [MySQL] My brain blew a fuse (multi-table select, joins(?))

Hi!

So, I'm working on a secret project thing which involves one record needing to be selected, along with info from a few other tables. The base record, "enhancement" needs to grab the origin(s) from the origin table, using the enhancement/origin join table, as well as effect(s) from the effect table, using the enhancement/effect join table, and finally, the server using the server_id and server table.

I want this:
Enhancement table values
Origin(s) - from the origin table
Server - from the server table
Effect(s) - from the effect table

Schemas:

Code:

mysql> desc coh_enhancement;

+----------------+-------------------------------------+------+-----+---------+----------------+

| Field          | Type                                | Null | Key | Default | Extra          |

+----------------+-------------------------------------+------+-----+---------+----------------+

| enhancement_id | int(11)                             |      | PRI | NULL    | auto_increment |

| server_id      | int(11)                             |      |     | 0       |                |

| en_level       | int(2)                              |      |     | 0       |                |

| en_display     | varchar(254)                        | YES  |     | NULL    |                |

| en_comment     | text                                | YES  |     | NULL    |                |

| en_type        | enum('training','dual','single')    | YES  |     | NULL    |                |

| price          | int(11)                             |      |     | 0       |                |

| availability   | enum('for sale','sold','no sale')   | YES  |     | NULL    |                |

| status         | enum('active','inactive','deleted') | YES  |     | NULL    |                |

+----------------+-------------------------------------+------+-----+---------+----------------+

9 rows in set (0.00 sec)



mysql> desc coh_enhancement_effect;

+-----------+--------------+------+-----+---------+----------------+

| Field     | Type         | Null | Key | Default | Extra          |

+-----------+--------------+------+-----+---------+----------------+

| effect_id | int(11)      |      | PRI | NULL    | auto_increment |

| effect    | varchar(128) | YES  |     | NULL    |                |

+-----------+--------------+------+-----+---------+----------------+

2 rows in set (0.00 sec)



mysql> desc coh_enhancement_effect_join;

+----------------+---------+------+-----+---------+-------+

| Field          | Type    | Null | Key | Default | Extra |

+----------------+---------+------+-----+---------+-------+

| enhancement_id | int(11) |      |     | 0       |       |

| effect_id      | int(11) |      |     | 0       |       |

+----------------+---------+------+-----+---------+-------+

2 rows in set (0.00 sec)



mysql> desc coh_origin;

+-------------+--------------+------+-----+---------+----------------+

| Field       | Type         | Null | Key | Default | Extra          |

+-------------+--------------+------+-----+---------+----------------+

| origin_id   | int(11)      |      | PRI | NULL    | auto_increment |

| origin      | varchar(128) | YES  |     | NULL    |                |

| description | text         | YES  |     | NULL    |                |

+-------------+--------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)



mysql> desc coh_enhancement_origin_join;

+----------------+---------+------+-----+---------+-------+

| Field          | Type    | Null | Key | Default | Extra |

+----------------+---------+------+-----+---------+-------+

| enhancement_id | int(11) |      |     | 0       |       |

| origin_id      | int(11) |      |     | 0       |       |

+----------------+---------+------+-----+---------+-------+

2 rows in set (0.00 sec)



mysql> desc coh_server;

+-----------+--------------+------+-----+---------+----------------+

| Field     | Type         | Null | Key | Default | Extra          |

+-----------+--------------+------+-----+---------+----------------+

| server_id | int(11)      |      | PRI | NULL    | auto_increment |

| name      | varchar(128) | YES  |     | NULL    |                |

+-----------+--------------+------+-----+---------+----------------+

2 rows in set (0.00 sec)

So far I have just the table / field declarations, and my brain is getting fried on everything after the "WHERE" :P I will now go to sleep and look at it again this morning, with clearer head and vision.

Here's my query right now:

Code:

SELECT e.*, o.origin, s.name, f.effect

    FROM coh_enhancement as e,

         coh_origin as o,

         coh_server as s,

         coh_enhancement_effect as f,

         coh_enhancement_effect_join as fj,

         coh_enhancement_origin_join as oj

    WHERE

         fj.effect_id = 1 AND

         oj.origin_id = 1 AND

         e.server_id = 1

    GROUP BY

         enhancement_id;

This returns the one result that I have in the db, with all fo the fields merged, however the resulting entry for "origin" contains only one of the two values:

Code:

mysql> SELECT * from coh_enhancement_origin_join;

+----------------+-----------+

| enhancement_id | origin_id |

+----------------+-----------+

|              1 |         1 |

|              1 |         4 |

+----------------+-----------+

2 rows in set (0.00 sec)

..and my query results:

Code:

+----------------+-----------+----------+----------------------------------+---------------------------+---------+-------+--------------+--------+--------+---------+----------+

| enhancement_id | server_id | en_level | en_display                       | en_comment                | en_type | price | availability | status | origin | name    | effect   |

+----------------+-----------+----------+----------------------------------+---------------------------+---------+-------+--------------+--------+--------+---------+----------+

|              1 |         1 |       24 | lvl 24 Mut/Mag Healing DO CHEAP! | I am too sexy for my hat. | dual    |  1000 | for sale     | active | Mutant | Victory | Accuracy |

+----------------+-----------+----------+----------------------------------+---------------------------+---------+-------+--------------+--------+--------+---------+----------+

1 row in set (0.00 sec)

Assume that we're being provided with an origin id, server id, and effect id with which to narrow down the list of enhancements. Any hints are appreciated.