Your main problem is giving every
individual book a unique id. You appear to have a model that looks a little bit like this..
declare @book_table table (book_id int identity(1,1), book_name nvarchar(255), book_author nvarchar(255), cats_id int, book_stock int);
insert into @book_table(book_name, book_author, cats_id, book_stock) values
('VB6 - The recurring nightmare','CHill60', 14,1),
('VB6 - The recurring nightmare','CHill60', 14,1),
('VB6 - The recurring nightmare','CHill60', 14,1);
update @book_table set book_stock = 0 where book_id = 1;
select * from @book_table;
Giving you data that looks like this
book_id book_name book_author cats_id book_stock
1 VB6 - The recurring nightmare CHill60 14 0
2 VB6 - The recurring nightmare CHill60 14 1
3 VB6 - The recurring nightmare CHill60 14 1
If you wanted to see how much of each book name is left then you could do something like this
select book_name , book_author, cats_id, SUM(book_stock) as current_stock
from @book_table
group by book_name , book_author, cats_id
order by SUM(book_stock) desc;
Note the use of GROUP BY so that we can sum the "flag"
book_stock
over all the rows.
But that is not a good database model. It is more usual to give the "book" the
id
number - i.e. each
book_name
has an id, and
book_stock
represents the current number of copies of that book in stock. E.g.
declare @book_table table (book_id int identity(1,1), book_name nvarchar(255), book_author nvarchar(255), cats_id int, book_stock int);
insert into @book_table(book_name, book_author, cats_id, book_stock) values
('VB6 - The recurring nightmare','CHill60', 14,3);
select book_name , book_author, cats_id, book_stock from @book_table;
The data looks like
thisbook_name book_author cats_id book_stock
VB6 - The recurring nightmare CHill60 14 3
To sell a book, deduct one from the current stock e.g.
update @book_table set book_stock = book_stock - 1 where book_id = 1;
And to display you could do something like this
select book_name , book_author, cats_id, case when book_stock = 0 then 'Out of Stock' else cast(book_stock as varchar(30)) end as current_stock
from @book_table
order by book_stock desc;
Note only an
order by
is required now - no grouping required.
N.B. I have included the 'Out of Stock' in the SQL just to show how easy it is to still list the book but show it as out of stock. You would not normally do this in the SQL but have something in your UI layer that does that instead.