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