Darou: One thing I’m going to want to do later is ‘sort players by total xp’
Pisani: Volund: select player,sumxp as sumxp from tbl group by player order by sumxp desc;
Monroe: You have NO idea how insanely tough that is to do with my previous method. or how inefficient it is.
Sigley: I need to look up what ‘group by’ does.
Gensler: So I am trying to do the following: 1 Find all userids for which field8 in userfield table contains ABC, 2 Search through the user table to find all matching userids, 3 From the results in user table, only show those whose usergroupid or secondgroupid the latter being a comma separated array do not match a predefined array of user group IDs, 3 Then for all that match, append another ID onto their secondgroupd.
Barninger: At the moment I am just trying SELECT * and it is giving me this error, but I am not even sure if the query itself will do what i am after:
Stiff: SELECT * FROM user JOIN SELECT userid FROM userfield WHERE field LIKE ‘%ABC%’ criteria ON user.userid=criteria.userid WHERE user.usergroupid NOT LIKE ‘%X%’, ‘%Y%’ OR user.secondgroupid NOT LIKE ‘%X%’, ‘%Y%’
Poppema: Error: #1241 – Operand should contain 1 columns
Bachinski: Volund: https://dev.mysql.com/doc/refman/5.6/en/select.html https://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html
Stelmack: Kummerow: That’s my query
Counihan: Again with the insanely dense do***entation. : I’ll try to read it though
Vaka: UPDATE user JOIN SELECT userid FROM userfield WHERE field LIKE ‘%ABC%’ criteria ON user.userid=criteria.userid WHERE user.usergroupid NOT LIKE ‘%X%’, ‘%Y%’ OR user.secondgroupid NOT LIKE ‘%X%’, ‘%Y%’ SET secondgroupid CONCATsecondgroupid, ‘,Z’
Bachinski: Intent: you’re violating very basic normalization requirements. your sql will be tortured at best, and never work at worst
Kummerow: 19:00 Kummerow when you do, post it and your example schema to sqlfiddle.com
Kummerow: 19:00 Kummerow it’s a useful tool for query exploration
Bachinski: Volund: if reading isn’t your strength, you might consider a different hobby
Joerger: Unfortunately, I can’t change the table structure
Ginsburg: Bachinski: It’s more like that particular website is anathema to my thought processes. I have no trouble with like, the python docs, or PennMUSH softcode, or lots of other things.
Bachinski: There is an easy way and a hard way to learn SQL. Unfortunately, the easy way doesn’t work.
Rompf: Volund: MySQL Tutorial: http://dev.mysql.com/doc/refman/5.6/en/tutorial.html SQL Tutorials: http://www.sqlcourse.com http://tut.php-quake.net/en/mysql.html and sqlzoo.net
Evola: THANK YOU that’s better.
Linville: Although I think I grok what GROUP BY does now. Looks like it creates ‘groups’ based on the distinct values of a given field and aggregates the respective fields for corresponding rows?
Bachinski: Volund: the O’Reilly book on “Learning SQL” is pretty good too
Bachinski: Volund: yes. beware that mysql doesn’t follow the sql standard by default when it comes to GROUP BY.
Bachinski: Volund: normally you cannot mix grouped and ungrouped columns in the SELECT clause of a query. mysql will allow that by default, but the ungrouped column’s value is unpredictable
Cartagena: Fair enough. I am unlikely to be doing that.
Bachinski: Volund: select a, sumb from t1 group by a; — that’s fine
Pluma: Intent: by saying ‘i can’t change the structure’, is really saying I’ve got **** and I would like you to do magic with it. its like trying to performance tune a 3 wheeled car running on one piston without changing the engine. Or teach a stupid cripped dog to do trick. need more metaphores?
Bachinski: Volund: select a, sumb, c from t1 group by a; — there’s no predicted what value will be in column c
Bachinski: Volund: there are solutions to this, however. luckily I won’t bore you with them. :-
Wyly: Danblack: I get it. Unfortunately, is the truth. It’s a big script, and there’s too much that depends on the structure to change it, so I have to deal with it.