Multiple users allowed books to be rented Library

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Multiple users allowed books to be rented Library

Author

Topic

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-06-05 : 03:35:13

Library application needs help on a query. This is MySQL

A member is allowed a certain amount of books to be rented. A single table stores the ones he wants, ones that have been checked out already and ones that have been checked in i.e. returned by the user.

In a single query we need to display for the librarian a list of only the books that are desired WHILE maintaining the integrity in the difference between how many are allowed out for the user and how many are checked out. If you have 2 books desired, 1 out. Then only 1 book desired should display. Each user has a varied amount.

Books table (stores books)


ID     booktitle  , etc.
56      Huckleberry Fin
57      Harry Potter
58      To Kill a Mockingbird
59      Tale of Two Cities

Accounts table (stores accounts)


ID     Username  etc
23        Jim
24        Larry

Account_Type table (stores how many rentals a user is allowed out)


ID    Accttype
23         2
24         1

Rentals table (this stores rentals desired (checkedout=0, checkedin=0), rentals checked out but not checked in (checkedout=1 checkedin=0), or completely fulfilled (checkedout=1, checkedin=1))


RentalIDBookID    AccountID                   checkedout     checkedin
12      56              23                    1                    0
13      57              23                    0                    0
14      58              23                    0                    0
15      59              24                    0                    0

I want to query the rentals to know which books are ok to check out. From rentals, I only can check out rental id's 13 & 15 NOT 14 since he has one out already and only is allowed one more. In my query 13, 14, and 15 all come up however.

13 & 15 only should come up only.

So far, here is my query.


SELECT Rentals.bookid, Rentals.rentalid, Rentals.checkedout, Rentals.checkedin, Movies.mov_title_eng, Accounts.fname, Accounts.accountid, Account_Types.accttype

FROM Rentals, Books, Accounts, Account_Types

WHERE Rentals.checkedout = 0 AND Rentals.checkedin = 0 AND Rentals.bookid = Books.bookid AND Books.accountid = Accounts.accountid AND Accounts.accountid = Account_Types.accountid
ORDER BY  Rentals.rentalid ASC

It works fine when a user is allowed a max of 1 rental but not if user is allowed a max of 2 or more such that if he has 1 out and has 2 desired rentals both his desired rentals show up in the query.

We can manually check to see how many more are allowed but I'd rather just get rid of it from displaying.

Other things I've tried such as GROUP BY just seem to totally knock out accountid 23 from the list altogether instead giving me the one left over he is allowed out, along with the ones for other accounts.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-05 : 03:44:57

quote:
This is MySQL

then post in a MySQL forum not here.

KH
[spoiler]Time is always against us[/spoiler]

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-06-05 : 03:56:56

I realize it's a tough one.

Im stuck with MySql for this project. I usually use MSSQL...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-05 : 04:23:56

this is for SQL Server 2005. The only function that i used that is particular to SQL Server is row_number() over ( partition by .. order by .. ).
See if there is a similar function in MySQL. If not you can implement that using subquery (select count(*) from rentals where . . )


DECLARE @books TABLE
(
	id		int,
	booktitle	varchar(30)
)

INSERT INTO @books
SELECT	56, 'Huckleberry Fin'		UNION ALL
SELECT	57, 'Harry Potter'		UNION ALL
SELECT	58, 'To Kill a Mockingbird'	UNION ALL
SELECT	59, 'Tale of Two Cities'

DECLARE @accounts TABLE
(
	id		int,
	username	varchar(10)
)

INSERT INTO @accounts
SELECT	23, 'Jim'	UNION ALL
SELECT	24, 'Larry'

DECLARE @account_type TABLE
(
	id		int,
	accttype	int
)

INSERT INTO @account_type
SELECT	23, 2	UNION ALL
SELECT	24, 1

DECLARE	@rentals TABLE
(
	id		int,
	bookid		int,
	accountid	int,
	checkedout	int,
	checkedin	int
)

INSERT INTO @rentals
SELECT	12, 56, 23, 1, 0 UNION ALL
SELECT	13, 57, 23, 0, 0 UNION ALL
SELECT	14, 58, 23, 0, 0 UNION ALL
SELECT	15, 59, 24, 0, 0


SELECT	rentalid = r.id, r.bookid, b.booktitle, r.accountid, a.username, r.book_no
FROM	(
		SELECT	id, bookid, accountid,
			book_no	= row_number() OVER (PARTITION BY accountid ORDER BY id)
		FROM	@rentals r
		WHERE	checkedin	= 0
	) r
	INNER JOIN @account_type t	ON	r.accountid	= t.id
	INNER JOIN @accounts a		ON	r.accountid	= a.id
	INNER JOIN @books b		ON	r.bookid	= b.id
WHERE	r.book_no	<= t.accttype

/*
rentalid    bookid      booktitle                      accountid   username   book_no              
----------- ----------- ------------------------------ ----------- ---------- -------------------- 
12          56          Huckleberry Fin                23          Jim        1
13          57          Harry Potter                   23          Jim        2
15          59          Tale of Two Cities             24          Larry      1

(3 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-05 : 04:34:11

[code]
SELECT rentalid = r.id, r.bookid, b.booktitle, r.accountid, a.username, r.book_no
FROM (
SELECT id, bookid, accountid,
book_no = (SELECT COUNT(*) FROM @rentals x WHERE x.accountid = r.accountid AND x.id <= r.id)
FROM @rentals r
WHERE checkedin = 0
) r
INNER JOIN @account_type t ON r.accountid = t.id
INNER JOIN @accounts a ON r.accountid = a.id
INNER JOIN @books b ON r.bookid = b.id
WHERE r.book_no <= t.accttype
[/code]

KH
[spoiler]Time is always against us[/spoiler]

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-06-05 : 10:02:38

I'm getting errors on book_no. What does that stand for. I have no column with that name.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-05 : 10:05:19

book_no is a column alias to determine, the 1st book or 2nd etc books on rental.

You mean the error is from MySQL or MSSQL ?

Sorry, i can't help you with MySQL. Not familiar with that at all.

KH
[spoiler]Time is always against us[/spoiler]

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-06-05 : 15:12:52

hmm. You're calling book_no on the first line. It hasn't been identified as an alias yet however?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-05 : 19:41:47

book_no is defined in the derived table r

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources