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

- Tilted Technology (https://thetfp.com/tfp/tilted-technology/)

- - [MySQL] My brain blew a fuse (multi-table select, joins) (https://thetfp.com/tfp/tilted-technology/55870-mysql-my-brain-blew-fuse-multi-table-select-joins.html)

[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.

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?

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. :)

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.

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.

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.