Support

  1. 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.
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 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 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 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.

Request Support

Support is currently Online

Support Availability

Working days: Monday to Friday. The support staff is not available on weekends; in the most of cases tickets will not be answered during that time.

Reply time: Depending on the complexity of your support issue it's usually between a few minutes and 24 hours for paid members and about one week for free members. When we expect longer delays we will notify you.

Guidelines

Before you post: read the documentation and search the forums for an answer to your question.

When you post: include Site Details if you request a support (you can use the form below the reply in Site Details tab).

Auto Solved Question: If after a week the author of the post does not reply to a request by moderator, the question will be marked as resolved.

Language: only English

Search Users

Easy ProfileĀ® is not affiliated with or endorsed by Open Source Matters or the Joomla Project. Joomla is Free Software released under the GNU/GPL License.