Donivan: Although, I don’t think what I’m trying to do is all that bizarre?
Doten: Bachinski: Eh. that’s unlikely to come up thankfully! I’m not using tons of stuff.
Kalvaitis: I think I just don’t know how to accomplish it.
Gainous: Oooookay I totally see what GROUP BY does now
Murri: Just ran some totally useless queries on some of my existing tables to see what it spat out. Veeeery cool feature.
Bachinski: Volund: select a, sumb from t1 group by a with rollup; — try that ‘with rollup’ thing
Burrington: That gave me a fifth field that appears to have the sum of EVERYTHING.
Bachinski: Volund: in addition to sum and count, there are a variety of very useful aggregate functions for use with GROUP BY https://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html
Leitner: Is there a limit to which you can stack OR conditions? I have three of them which worked perfectly on their own, and work perfectly when there are only two, but you and the third one, and none of them return the results are supposed to.
Calleja: SELECT * FROM user WHERE membergroupids NOT LIKE ‘7,%’ OR membergroupids NOT LIKE ‘%,7,%’ OR membergroupids NOT LIKE ‘%,7’;
Brandler: I’m trying to get around the crappy table structure
Paske: Do a put a WHERE before or after a GROUP BY. after, I think?
Porrello: Err, no, before. *checks Niedzwiedzki*
Bachinski: Intent: sql statements are limited to the max_allowed_packet config setting. you are nowhere near it
Ozment: So I should be able to do THIS. 😀
Hails: SELECT objid,sumxp_amount as availxp,sumCASE WHEN xp_amount0 THEN xp_amount ELSE 0 END as gainxp,sumCASE WHEN xp_amount0 THEN xp_amount ELSE 0 END as spendxp FROM $EXPERIENCE$ WHERE xp_type=? GROUP BY objid ORDER BY gainxp desc
Yablonski: So why would one or two statements work, but not three
Bachinski: Intent: think logically. does your complex WHERE clause eliminate all rows?
Bachinski: Intent: if your sql does not result in an error, then you have a logic problem
Bachinski: Intent: more exactly, if your recordset is composed or zero rows, then that is what matches the logic of your query
Bachinski: Composed of zero rows*
Bachinski: Anyway, enough for the night. goodluck, ciao
Wironen: Bachinski: with all the where clauses, I get the full set of data back, including the rows that violate each individual clause
Bachinski: Intent: use AND instead of OR
Bachinski: Intent: AND means all conditions much match. OR means any condition may match
Wassel: AND work, but I’m confused-for each result, only one of those are true, therefore wouldn’t OR be what’s applicable? There is no row for which all three statements match.
Hollinshed: These are strange times…
Bottom: Now to put some more sql-fu to work
Scicchitano: Unfortunately I might -have- to break this up into multiple queries due to limitations in the MUSHcode I’m working with, but. oh well.
Delawder: It works fine for now.
Tredinnick: I’ve got two queries now, which successfully worked to get their respective pieces of data. How do I get the second query to search only from the result set up the first?
Zachman: SELECT * FROM user LEFT JOIN userfield ON user.userid = userfield.userid WHERE userfield.fieldX LIKE ‘%Value%’;
Terracciano: SELECT * FROM user WHERE usergroupid NOT IN ‘1’,’2′ AND membergroupids NOT LIKE ‘2,%’ AND membergroupids NOT LIKE ‘%,2,%’ AND membergroupids NOT LIKE ‘%,2’;
Ferracioli: Intent: 1 derived table 2 In your case, just add that logic to the WHERE clause with an AND expression.
Tatlock: Intent: 3 Your second query is wrong or you don’t understand what LEFT JOIN does.
Venezia: 00:46 aradapilot_: the most straightforward method is just to use a derived table. select stuff from select stuff from.where. where. – but that can be slow. if there’s an easy way to do it, combine the logic of both.