Harison: Xgc: I understand. I’m also thinking about the database structure, and it looks like it would be less intensive to search the opposite way. I tried this, but it says I have illegal Niedzwiedzki: SELECT * FROM user WHERE user.usergroupid NOT IN ’30’,’52’ AND user.membergroupids NOT LIKE ‘7,%’ AND user.membergroupids NOT LIKE ‘%,7,%’ AND user.membergroupids NOT LIKE ‘%,7’ AND INNER JOIN userfield ON user.userid = userfield.userid WHERE userfield.fieldX LIKE ‘%Value%
Rabadan: I’m looking into update syntax
Rumery: Intent, you have two WHERE in there
Mcmarlin: Intent: SELECT . FROM . JOIN . WHERE . not joins after WHERE.
Rumery: And a JOIN “inside” the first WHERE
Jaggie: So I have to join first no matter what, but I can just changed the location of the conditional
Frain: Intent: Just look at the general Niedzwiedzki. JOINs are in the FROM clause.
Tannenbaum: Intent: No. Join criterial remains in the FROM clause. WHERE clause is just further filtering, not strictly join criteria.
Isales: So I’m stuck doing the join first?
Graft: Intent: For an UPDATE, that join criteria is in the table_reference section.
Ronquillo: Intent: The general SQL form allows more complex logic. You can have a WHERE clause inside the first table reference. then join after that.
Rumery: Intent, JOIN says which tables you are interested in and how you want them combined ON, WHERE says how to limit the results of that – but sql is not “executed” from left to right – it is declarative – you only tell it what results you want and optimizer will try some reasonably good way to get them for you
Damboise: Rumery: that is useful, thank you. figured out that I could move the condition, and did so to the end, and the execution time was the same for both queries, so I understand what you’re saying.
Ottaway: I’m trying to figure out how to do this in an update now
Lickiss: Intent: You can only do that for inner joins, freely move criteria between ON clause and WHERE clause.
Fleer: Intent: For more complex logic, you would need derived tables or CTE.
Gonalez: UPDATE user as US INNER JOIN userfield AS UF ON user.userid = userfield.userid SET membergroupids = IFmembergroupids= ”, ‘Z’, CONCATmembergroupids, ‘,Z’ WHERE userfield.fieldX LIKE ‘%Value%’ and user.usergroupid NOT IN ’30’,’52’ AND user.membergroupids NOT LIKE ‘7,%’ AND user.membergroupids NOT LIKE ‘%,7,%’ AND user.membergroupids NOT LIKE ‘%,7’;
Griess: UPDATE user AS us INNER JOIN userfield AS uf ON us.userid = uf.userid SET us.membergroupids = IFus.membergroupids= ”, ‘Z’, CONCATusmembergroupids, ‘,Z’ WHERE uf.fieldX LIKE ‘%Value%’ and us.usergroupid NOT IN ’30’,’52’ AND us.membergroupids NOT LIKE ‘7,%’ AND us.membergroupids NOT LIKE ‘%,7,%’ AND us.membergroupids NOT LIKE ‘%,7’;
Lobregat: Xgc: is that Looking right?
Curlee: Intent: You probably should consider getting rid of the comma separated lists from hell.
Knehans: I wish I could. There’s too much link to them, and it’s too big a script, for me to do anything about it
Blow: I’m looking into changing “in” to “exists”, but aside from that, does that all look alright?
Vidrio: Intent, a redesign would be quicker, better, time to bite the bullet
Speiden: In this part of my query, there are ways to replace the number seven with an array, instead of repeating these three conditions over and over for each number I want to put? AND vb_user.membergroupids NOT LIKE ‘7,%’ AND vb_user.membergroupids NOT LIKE ‘%,7,%’ AND vb_user.membergroupids NOT LIKE ‘%,7
Angerer: Or is that the price I pay for having a comma-delineated column
Ohlhauser: Intent: what Vidrio said. bite the bullet and get a test structure of some sence and some migrations and a new script to go with it.
Ducote: You will continue to have many more days of crappy impossible queries otherwise.
Waltjen: Its annoying enough just watching it without being you.
Burrola: Danblack: it’s just impossible With the current cir***stances. the script is just too big, and the resources at my disposal time to small to do that.