Toggle Submenu
  1. Forums
  2. Recent
  3. Categories
  4. Tags
  5. Users
  6. Badges
Support is currently Offline
Support Offday
Today is our off day. We are taking some rest and will come back stronger tomorrow. Promise!
Official support hours:
Monday To Friday except Saturday and Sunday
From 09:30 To 17:00
Our current time: Saturday 6:55 pm
Add a reply View Replies (4)

Resolved Select statement for LIKE one field and NOT LIKE another

  1. arcascomputing
    arcascomputing
  2. Support
  3. Tuesday, September 05 2017, 12:59 PM
I'm currently trying to get an SQL select query to work for a users listing module but am having difficulties.

I have two fields, both checkbox lists with several options. The first notes which research groups the users are in and the second selects if they are group lead for any research groups, so the first field is called "cluster" and the second is "group_coordinator".

I had the query running okay to select from the research group (cluster LIKE "%Cultural Heritages%") but now I also need to add in a selector for group_coordinator NOT LIKE "%Cultural Heritages%". So the query should select all folk who have been checked as being in the Cultural heritages group but should exclude those noted as being the group coordinator.

I tried ((cluster LIKE "%Cultural Heritages%";) AND (group_coordinator NOT LIKE "%Cultural Heritages%))" without any joy.
Responses (4)
admin
admin Accepted Answer
Admin
Hi,
there is an error at the end of your query, you should replace this:


Heritages%))"

with this


Heritages%"))
  1. more than a month ago
  2. Support
  3. # 1
arcascomputing
arcascomputing Accepted Answer
Pro
Apologies that was just me typing it in wrong (i also said both were checkboxes when they are in fact both select lists with multiples allowed).

The query I'm using is:


((cluster LIKE "%Cultural Heritages%") AND (group_coordinator NOT LIKE "%Cultural Heritages%"))

Which would select all users for whom the option "Cultural Heritages" has been selected in the "cluster" field and the option "Cultural Heritages" has not been selected in the "group_coordinator" field.

The above query, however, brings up a blank listing just now so still doesn't seem right.
  1. more than a month ago
  2. Support
  3. # 2
admin
admin Accepted Answer
Admin
Hi,
query seems correct, please check these:
- "group_coordinator" alias is correct
- your mysql DB do not recognize "NOT LIKE", you can try same query without "NOT", only to check if it works.

No results mean 2 possibilities:
1 No result (of course, but in your case this should not be possible because query cluster LIKE "%Cultural Heritages%" works)
2 Db error
  1. more than a month ago
  2. Support
  3. # 3
arcascomputing
arcascomputing Accepted Answer
Pro
Have managed to resolve this with the following query instead:


cluster LIKE "%Cultural Heritages%" AND ((group_coordinator IS NULL) OR (group_coordinator NOT LIKE "%Cultural Heritages%"))

I suspected that perhaps the query was having an issue with null values, so was matching if the field had a value selected for "group_coordinator" other than the one in the statement but had a value set. It, however, wasn't matching if there was no value set at all for that field, so I added in the "or null" part of the statement and this works now.
  1. more than a month ago
  2. Support
  3. # 4
  • Page :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.