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

seretogis · 05-16-2004, 11:51 PM

I had a brain fart and posted this in the Computer forum by mistake. Here it is, again, in the correct forum:

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:

*yoink*

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:

*yoink*

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:

*yoink*

..and my query results:

Code:

*yoink*

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.

Moskie · 05-17-2004, 02:47 AM

A shot in the dark...

isn't the GROUP BY command lumping all the records together that have the same enhancement_id? If that's the case, then all the results are merged into that one record being returned... do you mean to have ORDER BY?

EDIT:
or maybe you need to be more specific on *which* enhancement_id you want to group by?

seretogis · 05-17-2004, 03:09 AM

Quote:

Originally posted by Moskie
A shot in the dark...

isn't the GROUP BY command lumping all the records together that have the same enhancement_id? If that's the case, then all the results are merged into that one record being returned... do you mean to have ORDER BY?

EDIT:
or maybe you need to be more specific on *which* enhancement_id you want to group by?

Yeah, I think the GROUP BY usage is definitely a factor in the problem, but I'm not sure how else it could be used, or what it should be replaced with. If I eliminate the GROUP BY clause completely, my query returns, literally, 1500 results -- every possible combination of server/origin/enhancement effect. If I change the enhancement_id to e.enhancement_id or fj or oj, I get the same result as I get with just "GROUP BY enhancement_id". Thanks for the response.

Fallon · 05-17-2004, 06:08 PM

Now, I'm probably wrong, but don't you want to use a join statement in there at least once.
I think you need to link the tables to each other because I don't see how they are getting linked. Sorry I'm not that great of a db person.

magua · 05-17-2004, 07:08 PM

Not sure about the MySQL syntax. Too lazy to test. Caveat emptor.

I'm surprised MySQL lets you do that GROUP BY, since you don't have aggregate functions on the other columns. Maybe it automatically just uses the first or something.

Anyways.

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 e.enhancement_id = fj.enhancement_id
AND fj.effect_id = f.effect_id
AND e.enhancement_id = oj.enhancement_id
AND oj.origin_id = o.origin_id
AND e.server_id = s.server_id

Add other filters (fj.effect_id = 1, etc) at the end. No GROUP BY should be necessary, if I'm reading what you want right.

Note also that those are most likely inner joins, so if there's no matching data in one of the tables, you won't get any rows back. Can't recall the syntax for a left join in MySQL.

seretogis · 05-17-2004, 07:46 PM

Quote:

Originally posted by magua
Add other filters (fj.effect_id = 1, etc) at the end. No GROUP BY should be necessary, if I'm reading what you want right.

Note also that those are most likely inner joins, so if there's no matching data in one of the tables, you won't get any rows back. Can't recall the syntax for a left join in MySQL.

Gratsi

This doesn't do exactly what I was looking for, but I think that I am just trying to cram too much into one select -- I'll use your query and then do a little data massaging with PHP. Thanks again.

05-16-2004, 11:51 PM	#1 (permalink)
seretogis Huggles, sir? Location: Seattle	[MySQL] My brain blew a fuse (multi-table select, joins) I had a brain fart and posted this in the Computer forum by mistake. Here it is, again, in the correct forum: 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: yoink 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: yoink 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: yoink ..and my query results: Code: yoink 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. __________________ 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 Last edited by seretogis; 05-20-2004 at 05:59 AM..

05-17-2004, 02:47 AM	#2 (permalink)
Moskie Pure Chewing Satisfaction Location: can i use bbcode [i]here[/i]?	A shot in the dark... isn't the GROUP BY command lumping all the records together that have the same enhancement_id? If that's the case, then all the results are merged into that one record being returned... do you mean to have ORDER BY? EDIT: or maybe you need to be more specific on which enhancement_id you want to group by? __________________ Greetings and salutations. Last edited by Moskie; 05-17-2004 at 02:52 AM..

05-17-2004, 07:08 PM	#5 (permalink)
magua Tilted	Not sure about the MySQL syntax. Too lazy to test. Caveat emptor. I'm surprised MySQL lets you do that GROUP BY, since you don't have aggregate functions on the other columns. Maybe it automatically just uses the first or something. Anyways. 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 e.enhancement_id = fj.enhancement_id AND fj.effect_id = f.effect_id AND e.enhancement_id = oj.enhancement_id AND oj.origin_id = o.origin_id AND e.server_id = s.server_id Add other filters (fj.effect_id = 1, etc) at the end. No GROUP BY should be necessary, if I'm reading what you want right. Note also that those are most likely inner joins, so if there's no matching data in one of the tables, you won't get any rows back. Can't recall the syntax for a left join in MySQL.

05-17-2004, 06:08 PM	#4 (permalink)
Fallon Junkie Location: RI	Now, I'm probably wrong, but don't you want to use a join statement in there at least once. I think you need to link the tables to each other because I don't see how they are getting linked. Sorry I'm not that great of a db person.