Mysql – Check and count if a column value is used in another column of same table

MySQLmysql-5.6query

I have a table like this

and looking for a result like this, where the last modified date of a particular id is shown that too if the check is 1. Here id 6 is not in the result because the check is 0, also the 11/4/19 entry of 1 is ignored as the check is 0, so 9/4/19 entry is shown in result.

Next is the children count. it counts the number of times,the parent id is repeated. I really don't want the number of times,the parent id is repeated,but just want to know if its has child or not.

I am confused how to get the children count. Remaining i did like this

SELECT id, modified,checks 
FROM ( select * 
       from test 
       order by modified desc) as t1 
where checks!=0  group by (id)

and i got this result

How to get children count too or just if the id got a child?
How to get a result like this

Best Answer

SELECT t1.id, 
       MAX(CASE WHEN t1.`check` THEN t1.modified_date END) modified_date,
       COUNT(DISTINCT t2.id) children_count,
       1 `check`
FROM test t1
LEFT JOIN test t2 ON t1.id = t2.parent_id
GROUP BY t1.id
HAVING modified_date IS NOT NULL;

fidddle

Related Solutions

Mysql – Using count in triggers to update another table

This trigger does the job:

create trigger trx_updates_atrig
after insert on trx_updates for each row begin
DECLARE updatecount INT;
  set updatecount = ( select count(*) from trx_updates where request = new.request );
  if updatecount=4
    then
      update trx_request set finished=NOW() where id = new.request;
  end if;
end//

SQLFiddle Demo.

MySQL – Difference between using count(*) and information_schema.tables for counting rows

There are various ways to "count" rows in a table. What is best depends on the requirements (accuracy of the count, how often is performed, whether we need count of the whole table or with variable where and group by clauses, etc.)

a) the normal way. Just count them.
```
select count(*) as table_rows from table_name ; 
```
Accuracy: 100% accurate count at the time of the query is run.
Efficiency: Not good for big tables. (for MyISAM tables is spectacularly fast but no one is using MyISAM these days as it has so many disadvantages over InnoDB. The "spectacularly fast" also applies only when counting the rows of a whole MyISAM table - if the query has a WHERE condition, it still has to scan the table or an index.)
For InnoDB tables it depends on the size of the table as the engine has to do either scan the whole table or a whole index to get the accurate count. The bigger the table, the slower it gets.
b) using SQL_CALC_FOUND_ROWS and FOUND_ROWS(). Can be used instead of the previous way, if we also want a small number of the rows as well (changing the LIMIT). I've seen it used for paging (to get some rows and at the same time know how many are int total and calculate the number of pgegs).
```
select sql_calc_found_rows * from table_name limit 0 ; 
select found_rows() as table_rows ;
```
Accuracy: the same as the previous.
Efficiency: the same as the previous.
c) using the information_schema tables, as the linked question:
```
select  table_rows
from    information_schema.tables
where   table_schema = 'database_name'
  and   table_name = 'table_name' ;
```
Accuracy: Only an approximation. If the table is the target of frequent inserts and deletes, the result can be way off the actual count. This can be improved by running ANALYZE TABLE more often.
Efficiency: Very good, it doesn't touch the table at all.
d) storing the count in the database (in another, "counter" table) and update that value every single time the table has an insert, delete or truncate (this can be achieved with either triggers or by modifying the insert and delete procedures).
This will of course put an additional load in each insert and delete but will provide an accurate count.

Accuracy: 100% accurate count.
Efficiency: Very good, needs to read only a single row from another table.
It puts however additional load to the database.
e) storing (caching) the count in the application layer - and using the 1st method (or a combination of the previous methods). Example: run the exact count query every 10 minutes. In the mean time between two counts, use the cached value.

Accuracy: approximation but not too bad in normal circumstances (unless for when thousands of rows are added or deleted).
Efficiency: Very good, the value is always available.

Best Answer

Related Solutions

Mysql – Using count in triggers to update another table

MySQL – Difference between using count(*) and information_schema.tables for counting rows

Related Question