This one is difficult to explain, much less solve:
Say I have one table: People, with a column: People_ID
I have a second table: Inventory, with a column Inventory_ID
These tables have a many-to-many relationship: i.e. one person can use many pieces in the inventory AND one piece of the inventory can be used by many people
So, I have established a third table: Inventory_use that has the following columns: People_ID, Inventory_ID, date_out, date_in
This table shows which People_ID抯 are related to which Inventory_ID抯 and the dates they were checked out and returned. This table will also be used to show the item's history of use (who used it and when).
so, here is the difficult part:
I want to create a list of all the pieces of inventory that are not currently in use:
i.e. All the Inventory_ID抯 from Inventory EXCEPT those that exist in Inventory_use WHERE date_out !=NULL AND date_in ==NULL
OK ?can I have that in SQL to go please?
OR- if you have a better way to solve this problem, let me know.
Say I have one table: People, with a column: People_ID
I have a second table: Inventory, with a column Inventory_ID
These tables have a many-to-many relationship: i.e. one person can use many pieces in the inventory AND one piece of the inventory can be used by many people
So, I have established a third table: Inventory_use that has the following columns: People_ID, Inventory_ID, date_out, date_in
This table shows which People_ID抯 are related to which Inventory_ID抯 and the dates they were checked out and returned. This table will also be used to show the item's history of use (who used it and when).
so, here is the difficult part:
I want to create a list of all the pieces of inventory that are not currently in use:
i.e. All the Inventory_ID抯 from Inventory EXCEPT those that exist in Inventory_use WHERE date_out !=NULL AND date_in ==NULL
OK ?can I have that in SQL to go please?
OR- if you have a better way to solve this problem, let me know.