this img represents two of tables i have on my mysql server one is called tlocation and the other one is temployee
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.