SQL Query returns incorrect count

Question

I am trying to get the count of all the feature and the feature name against an application by using MySQL query. Here is the query:
select t2.feature,count(t2.feature) as count from judge_task t1, approve t2 where t1.application_name='Retail Bank Portal' AND t2.gap_status=1 group by feature;
Here is the description of my tables:
Table judge_task

    +------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | int          | NO   | PRI | NULL    | auto_increment |

| application_id   | int          | NO   |     | NULL    |                |
| application_name | varchar(50)  | YES  |     | NULL    |                |
| feature_id       | int          | NO   |     | NULL    |                |
| feature_name     | varchar(50)  | YES  |     | NULL    |                |
| task_type        | int          | NO   |     | NULL    |                |

Table approve

+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | int          | NO   | PRI | NULL    | auto_increment |

| feature          | varchar(500) | NO   |     | 0       |                |


| gap_status       | int          | NO   |     | 3       |                |
+------------------+--------------+------+-----+---------+----------------+

There is no relationship like foreign key between both the tables. The database was designed by someone else and it can not be updated now as the application is almost completed. Updating the database will disturb the whole application design.
Here is the result of the query:

+---------+-----+
| feature | app |
+---------+-----+
| S3      | 175 |
| Login   | 875 |
+---------+-----+

The original count are as follows:
S3 = 1, Login = 5

I am not getting the exact query to get my results. Would you please suggest me a correction to my query? How should I update it to get my results? Thanks a lot Update 1

sample data for judge_task is here

| 100173 |              4 | Retail Bank Portal |         16 | Login                   |         1 |
| 100203 |             -1 | Retail Bank Portal |         16 | Login                   |         2 |
| 100204 |              4 | Retail Bank Portal |         19 | Bill Pay                |         2 |
| 100205 |             -1 | Retail Bank Portal |         16 | Login                   |         2 |
| 100206 |             -1 | Retail Bank Portal |         16 | Login                   |         2 |
| 100207 |             -1 | Retail Bank Portal |         16 | Login                   |         2 |
| 100208 |             -1 | Retail Bank Portal |         16 | Login                   |         2 |
| 100209 |             -1 | Retail Bank Portal |         22 | S3                      |         2 |

Sample data for approve

|  59 | Login                   |          1 |
|  60 | Login                   |          1 |
| 115 | Login                   |          1 |
| 116 | Login                   |          1 |
| 117 | Login                   |          3 |
| 118 | Login                   |          3 |
| 119 | Login                   |          3 |
| 120 | Login                   |          3 |
| 121 | Login                   |          3 |
| 122 | Login                   |          3 |
| 123 | Login                   |          3 |
| 124 | Login                   |          3 |
| 125 | Login                   |          3 |
| 126 | Login                   |          3 |
| 127 | Login                   |          3 |
| 128 | Login                   |          1 |
| 129 | S3                      |          1 |
+-----+-------------------------+------------+

I have updated the table definitions according to sample data removing the irrelevant columns. Thanks

Update
One thing I forget to mention that the data has to be selected from approve table and just the application name is to be selected from judge_task.

user_15011154 · Answer

Here is how I'd make the query:

SELECT DISTINCT
  t.feature, t.feature_count
FROM judge_task
  INNER JOIN (
    SELECT
      app.feature, COUNT(*) AS feature_count
    FROM approve app
    WHERE app.gap_status = 1
    GROUP BY app.feature
  ) AS t ON t.feature = judge_task.feature_name
WHERE judge_task.application_name = 'Retail Bank Portal'
;

You can see the result in this SQLFiddle

With the inner select you can avoid duplication of result due to the Cartesian product with the approve table

user_3469391 · Answer

You are performing a(n implicit) CROSS JOIN with your query, which means, all rows from table A are combined with table B in the background.

First find the way you can explicitly join your tables (feature_id = feature.id maybe?)

Use explicit join (INNER JOIN, LEFT JOIN, RIGHT JOIN, etc) instead of implicit one (listing the tables with comas in the FROM clause).

user_7405182 · Answer

First: you do not need a foreign key to join two tables in a query. All you need is a field in both tables with the "same data", that allows to acreate this relation, for example, a "TaskID" or anything like this (the name of the field is not nesessary the same). The only fields in your tables that allow to create this connection in your tables are: judge_task.feature_name and approve.feature. I guess we can try this query:

select t2.feature, count(t2.id) from judge_task as t1 
join approve as t2 on t2.feature = t1.feature_name
where t1.application_name='Retail Bank Portal'  AND t2.gap_status=1 
group by feature;

If your tables have many rows I recommend that you create indexes on judge_task.feature_name and approve.feature (as well as on judge_task.application_name) to improve performance.

SQL Query returns incorrect count

3 Answers