MySQL select statement giving me trouble

Question

I am having trouble with a MySQL query.

I have 3 tables: table1, table2 and table3.

What I want to do is get a distinct count of the codes from table1 where the date was in June 2013 and the it was of type "N" and where the status from table3 was never 'Confirmation'.

At the moment I am getting results that contain status of 'Confirmation' because there can be many status linked to a code. So I want to ensure that my result set never contained 'Confirmation'. Long winded, but I hope thorough.

Here is my query:

select count(distinct(code)) 
  from table1 
 where date between '2013-06-01' and '2013-07-01' 
   and type = 'N' 
   and id in (select id 
                from table2 
               where id_response in (select id_response 
                                       from table3 
                                      where status NOT LIKE 'Confirmation'));

I know my query is not ideally written, so I welcome all suggestions to help me make this better and get the results I need.

Thanks for the help.

Answer 1

I would do:

select table1.code, count(table1.code) 
from table1, table2, table3
where table1.date between '2013-06-01' and '2013-07-01'
and table1.type = 'N'
and table1.id = table2.id
and table2.id_response = table3.id_response
and table3.status <> 'Confirmation'
GROUP BY table1.code

Answer 2

Hope this will work :

select
table1.code, 
count(table1.code) 
from table1 
INNER JOIN table2 on (table1.id = table2.id)
where table1.date between '2013-06-01' and '2013-07-01' and table1.type = 'N' 
AND table2.id_response NOT IN 
(Select  table3.id_response from table3 WHERE table3.status = 'Confirmation')
GROUP BY table1.code