G.Del Castillo G.Del Castillo - 6 months ago 32
MySQL Question

compare several row in the same table to see if the status change according to time [mysql]

this img represents two of tables i have on my mysql server one is called tlocation and the other one is temployee

Hello community!

i hope someone can help me with my problem.
The table 'tlocation' is used to get the record of every movement of a item in the inventory of an xxxxx organization with the column 'TRANSACTION', such movement can be assignment or release to an employee or a department. The value (6) means assignment to a department and (7) means release of a department.
I have done the association between the item and department using the employee cause i can't add another column to the table. then i search the employee's 'ID_DEPARTMENT' to filter.

Here is the thing, right now i'm trying to make a query that set as result only the elements on the table 'tlocation' that currently are assigned, that has not been release. In the img you may see that the 'ID_ITEM'= 2 is on the table two times one for an assignment and for release to the employee (3) department (3).


select id_item,count(*) as theCount
from tlocation
where transaction in (6,7)
group by id_item
having theCount=1;

please correct for Linux case-sensitivity

the results rows are 2

| id_item | theCount |
|     103 |        1 |
|     176 |        1 |

Like I said in comments above, it assumes any 7 would have had a 6 prior. So it depends on knowing your data.