Count from a count (distinct)

Question

I have a query like this:

select * from questions as q  
    left join question_filters as qf on q.id = qf.question_id 
    left join users as u on q.user_id = u.id 
    left join trainers as t on q.trainer_id = t.id 
where q.deleted_at is null  
    and qf.filter_id in (3,4) 
    and q.status = 'published' 
    and q.published_at < now() 
    group by q.id
having count(distinct qf.filter_id) = 2

I have an "ALL IN" case, and I found this links works for me: https://stackoverflow.com/questions/7505045/mysql-in-operator-must-match-all-values.

But now I want to make a count for that same query: My problem: This query gaves me the error "Duplicate column name "ID" (all the tables in the join have ID)

select count(*) from ((select * from questions as q  
    left join question_filters as qf on q.id = qf.question_id 
    left join users as u on q.user_id = u.id 
    left join trainers as t on q.trainer_id = t.id 
where q.deleted_at is null  
    and qf.filter_id in (3,4) 
    and q.status = 'published' 
    and q.published_at < now() 
    group by q.id
having count(distinct qf.filter_id) = 2)) as first_query

Another attempt:

select count(q.id) from questions as q  
    left join question_filters as qf on q.id = qf.question_id 
    left join users as u on q.user_id = u.id 
    left join trainers as t on q.trainer_id = t.id 
where q.deleted_at is null  
    and qf.filter_id in (3) 
    and q.status = 'published' 
    and q.published_at < now() 
    group by q.id
having count(distinct qf.filter_id) = 1

returns many results, one for each result. Thank you

Answer 1

The first query has a group by q.id. While MySQL allows you to use select * in a group by query, it is not the best practice, unless you are 100% sure that you want the undesired side effects. If you wan to not have ill-determined results, you should only include in the select list columns are are functionally dependent on the group by columns/expressions.

In your case, that would be any column from questions, users or trainers because you group by the primary key of questions and the joins to the other 2 tables are based on foreign key relationship from questions. But you should not put any column from the question_filters because that join is on a reverse foreign key relationship.

If/when you move to version 5.7, you would get an error with the 1st query otherwise.

Also, the left join to question_filters is actually treated as inner join, due to the where condition (and qf.filter_id in (3,4)). You could also change that to be explicit. The query becomes:

select q.*, u.*, t.* 
from questions as q  
    join question_filters as qf on q.id = qf.question_id 
    left join users as u on q.user_id = u.id 
    left join trainers as t on q.trainer_id = t.id 
where q.deleted_at is null  
    and qf.filter_id in (3,4) 
    and q.status = 'published' 
    and q.published_at < now() 
    group by q.id
having count(distinct qf.filter_id) = 2 ;

---

Now for error you got with the count query: this is caused by the multiple columns with identical alias (id) in the subquery's select list. You can easily solve that by using select 1 or select q.* (or just select null).

What's more, the joins to users and trainers are redundant for that query (because they are left joins) and so can be removed. The query becomes:

select count(*) 
from 
   (select 1 from questions as q  
        join question_filters as qf on q.id = qf.question_id 
    where q.deleted_at is null  
        and qf.filter_id in (3,4) 
        and q.status = 'published' 
        and q.published_at < now() 
        group by q.id
    having count(distinct qf.filter_id) = 2
   ) as first_query ;