0

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

0
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 ;
1
  • Really, REALLY good info! Sorry for the extra left joins, the full query has more conditions, involving those tables. "select 1" worked great!! – German Jan 22 '16 at 15:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.