expression engine SQL AND OR query?

What i need to do is both an AND and OR in the same query, which i know is not possible so looking for an alternative, please help!

I have two category groups within ee, one is to determine what users are able to see that file and the other filters the file in to different groups.

What i need to do is select every file which is in category 19 (this is to determine the user group) and then i need the check it is in one of the filter categories. Unfortunately every time a new category is assigned to a file a new row is created within the table, example non working sql below.

SELECT c.cat_id, 
f.* 
FROM exp_file_categories c 
inner join exp_files f ON (c.file_id = f.file_id) 
where c.cat_id in (20, 21) 
AND c.cat_id = 19 
group by c.file_id

Solution

Not knowing your tables I assume you want to do the following

First: you want to get the list of file_id in the category 19:

SELECT
    file_id
FROM
    exp_file_categories
WHERE
    cat_id = 19

Now you want to see, which of these files have an association with the cat_ids 20 or 21:
You can do this using a subquery (either for the filtering)

SELECT DISTINCT
   f.file_id
   -- , other columns from exp_files
FROM
   exp_files f
INNER JOIN
   exp_file_categories c
ON
   f.file_id = c.file_id
WHERE
   c.file_id IN (20, 21)
AND
   f.file_id IN (
       SELECT
           c1.file_id
       FROM
           exp_file_categories c1
       WHERE
           c1.cat_id = 19
  )

You can also join the the outer query to the subquery. You've got to give the subquery an alias name for that.

Otherwise you could use two subqueries in your WHERE clause:

SELECT
    <field_list>
FROM
    exp_files
WHERE
    -- file_id is in the list of files associated with category 19
    f.file_id IN (
       SELECT
           c1.file_id
       FROM
           exp_file_categories c1
       WHERE
           c1.cat_id = 19
  )
AND
   -- file_id is in the list of files associated with category 20 and 21
    f.file_id IN (
       SELECT
           c2.file_id
       FROM
           exp_file_categories c2
       WHERE
           c1.cat_id IN (20, 21)
  )

You should examine the execution plans for those queries