How to get records with different values in same column AND condition on another column

We are trying to get filtered custom field data out of the Moodle database via SQL query. Unfortunately it's necessary to join multiple tables to get the data we need, because Moodle keeps its data this way. Also we can't manipulate the data structure. The database looks something like this (simplified):

// table mdl_customfield_field
ID  shortname
================
8   language
11  institutions

// table mdl_customfield_data
ID  fieldid instanceid  value
=============================
1   8       1           2
2   8       2           1
3   11      1           1,2
4   11      2           2,3
5   11      3           1

We tried to get every record that has language 1 AND institution 2 with the following code.

SELECT * FROM mdl_course c
JOIN mdl_customfield_data d ON c.id = d.instanceid
JOIN mdl_customfield_field f ON f.id = d.fieldid
WHERE (f.shortname = 'institutions' AND (d.value LIKE '%,2,%' OR d.value LIKE '2,%' 
OR d.value LIKE '%,2' OR d.value = '2'))
AND (f.shortname = 'language' AND d.value = '1')

Using an AND in the last line of the query, the result set does not contain the language data and an OR is showing too many results. How can we just get the course that satisfies both conditions in the result?

> How would we get every record that has language 1 AND institution 2?

This reads like a relational division problem. One option uses a correlated subquery that filters courses that have both fields available and set to the expected value:

select c.*
from mdl_course c
where (
    select count(*)
    from mdl_customfield_data d
    inner join mdl_customfield_field f on f.id = d.fieldid
    where d.instance_id = c.id
      and (
           ( f.shortname = 'institutions' and '2' = any string_to_array(d.value, ',') )
        or ( f.shortname = 'language'     and '1' = any string_to_array(d.value, ',') )
) = 2

You could easily expand the query to accommodate for more criteria, by adding more or predicates in the subquery and incrementing the expected count of matches accordingly (currently 2).

Note that storing a list of values in CSV format in a database column (as here in column value ot table mdl_customfield_field) is not good practice; there should be a separate table to store this 1-N relationship.

Maybe treat the institute and language as 2 tables

SELECT c.*
FROM mdl_course c
JOIN (
    SELECT d.instanceid AS courseid
    FROM mdl_customfield_data d
    JOIN mdl_customfield_field f ON f.id = d.fieldid AND f.shortname = 'language'
    WHERE d.value = '1'
) l ON l.courseid = c.id
JOIN (
    SELECT d.instanceid AS courseid
    FROM mdl_customfield_data d
    JOIN mdl_customfield_field f ON f.id = d.fieldid AND f.shortname = 'institutions'
    WHERE d.value ~ '\y2\y'
) l ON i.courseid = c.id

d.value ~ '\y2\y' the tilde is for matching regular expressions in SQL

The \y is used for word boundaries in postgresql, other databases use \b as the word boundary. This will search for a whole word or number, so 2 will match 2 but not 28 or 82. Useful for searching comma separated values in string fields.

You could also use d.value ~ '[[:<:]]2[[:>:]]'

If you are developing this in Moodle, then you could use Moodle's sql compatibility functions to make the SQL code generic

$regexp = $DB->sql_regex(true);
$regexwordbegin = $DB->sql_regex_get_word_beginning_boundary_marker();
$regexwordend = $DB->sql_regex_get_word_end_boundary_marker();

$where = "d.value {$regexp} '{$regexwordbegin}:institute{$regexwordend}'`

:institute pass as a parameter in Moodle