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

seretogis · 05-16-2004, 06:45 PM

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.

Rawb · 05-17-2004, 09:55 PM

Well, it's tough to say exactly what is going on here, except that this query is probably not going to scale well at all, it's doing a full cross-join accross six tables and then weeding out the unnecessary rows. MySQL is sometimes good at this, but normally when you spell it out with explicit LEFT JOIN queries, it has a lot easier of a time optimizing it.

Secondly, there are no constraints to make sure that the tables are even joined correctly, so they can join any way they want and you aren't going to get the results you expected. To fix that up you are going to add a lot of statements to your WHERE clause that look like this "oj.origin_id = o.origin_id" without those, you're just going to get a hump-o-data

Okay. So, you are getting exactly what you asked for. oj.origin_id = 1, there is only one row where that is true. So without knowing how many rows in the other places actually match that, I can't tell for sure, but I'm guessing it's exactly what you asked for. To see everything that it collects (beware, the result set from this query will get huge, and really, really fast) just take everything off in the WHERE clause that doesn't look like this "oj.origin_id = o.origin_id" (you did read the second point, didn't you?)

seretogis · 05-18-2004, 05:00 AM

Thank you for the response, but I posted this in the wrong forum. Mods, please delete this thread.

05-17-2004, 09:55 PM	#2 (permalink)
Rawb Crazy Location: Salt Town, UT	Sorry, I'm a big JOIN fan... Well, it's tough to say exactly what is going on here, except that this query is probably not going to scale well at all, it's doing a full cross-join accross six tables and then weeding out the unnecessary rows. MySQL is sometimes good at this, but normally when you spell it out with explicit LEFT JOIN queries, it has a lot easier of a time optimizing it. Secondly, there are no constraints to make sure that the tables are even joined correctly, so they can join any way they want and you aren't going to get the results you expected. To fix that up you are going to add a lot of statements to your WHERE clause that look like this "oj.origin_id = o.origin_id" without those, you're just going to get a hump-o-data Okay. So, you are getting exactly what you asked for. oj.origin_id = 1, there is only one row where that is true. So without knowing how many rows in the other places actually match that, I can't tell for sure, but I'm guessing it's exactly what you asked for. To see everything that it collects (beware, the result set from this query will get huge, and really, really fast) just take everything off in the WHERE clause that doesn't look like this "oj.origin_id = o.origin_id" (you did read the second point, didn't you?)

05-18-2004, 05:00 AM	#3 (permalink)
seretogis Huggles, sir? Location: Seattle	Thank you for the response, but I posted this in the wrong forum. Mods, please delete this thread. __________________ seretogis - sieg heil perfect little dream the kind that hurts the most, forgot how it feels well almost no one to blame always the same, open my eyes wake up in flames