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

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.

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.