create a conditional select

I have a table that keeps record of targets assigned to different employees for different products for each month and it has a status field which keeps record of whether assigned target had been approved or not.

status - 1>>Pending, 2>>Approved

Eg:

pdt_id month emp_id status
1        04   1       2
2        04   2       2
3        04   3       1

1        05   1       2
2        05   2       2
3        05   3       2

Now I want to generate a report which shows the only the month for which there are no pending approvals. i.e from the开发者_JAVA技巧 above data the report should only show '05' because its the only month in which all the request have been approved

if i provide condition select month where status='2' it will fetch both 04 and 05 but i want to fetch only 05 ...

Plea

SELECT month
  FROM myTable
  WHERE month NOT IN (
    SELECT month
    FROM myTable
    WHERE status = 1
  )

LEFT JOIN the table onto itself to find out matches and eliminate them.

SELECT 
  t1.`month` 
FROM
  your_table AS t1 
  LEFT JOIN your_table AS t2 
    ON t1.`month` = t2.`month` 
    AND t2.`status` = 1 
WHERE t2.month IS NULL
GROUP BY t1.month 

There might be more elegant ways of doing this, but it gets the job done.

Months with Approved statuses only:

SELECT DISTINCT month
FROM myTable a
WHERE NOT EXISTS
  ( SELECT *
    FROM myTable b
    WHERE a.month = b.month
      AND b.status <> 2
  )

Months without any Pending:

SELECT DISTINCT month
FROM myTable a
WHERE NOT EXISTS
  ( SELECT *
    FROM myTable b
    WHERE a.month = b.month
      AND b.status = 1
  )

There are usually 3 ways to do this kind of problem, (using NOT EXISTS, using NOT IN and using LEFT JOIN with NULL check). You already have answers for the other 2 ways.

In this special case, there's another (4th) way. If you never plan to add more statuses than the 1 and 2, this will also work:

SELECT month
FROM myTable
GROUP BY month
HAVING MIN(status) = 2

Just a final comment/question. Do you only store month in the table, and not year? Because if you also have a year field, the query will not show correct results, once you have data from more than one year in the table.

You can just select months that don't have state Pending:

select month from table_name as tb1 where 0 = (select count(*) from table_name as tb2 where tb2.state = '1' AND tb1.month = tb2.month)

I dont understand why dont you do?

... WHERE month = "05" AND status = 2;

and if you have another concept which is "approved" why not add it as a column and include it in the query as well?

Sometimes fixing a thing means redesigning your tables.