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.
| Author |
Topic |
|
jgonzalez14
Yak Posting Veteran
73 Posts |
 Posted - 2008-12-01 : 10:26:22
|
I am making a database for a library. I made a patron table, checkout table, publication table. My question is this, within the checkout table I have these fields: check out date, due date, ontime (yes/no), days overdue. Is this efficient? or should i have this: check out, due date, return date. Then with in the query use a date diff and find out how many days over the due date that is was. Once I find this I will link it do the fine table and multiply by the appropriate fine, based on what type the patron is. What should i go with?Also when I run this query I recieve 24 rows; however, I should only return 20 being there is only 20 patrons. For some reason it is returning duplicates. Can any one help with this?SELECT patron.fname, patron.lname, fine.fineFROM patron INNER JOIN Checkout ON patron.patronID = Checkout.patronID INNER JOIN publication ON Checkout.publicationID = publication.publicationID INNER JOIN patronType ON patron.patronTypeID = patronType.patrontypeID INNER JOIN fine ON patron.patronTypeID = fine.FineID |
|
|
jgonzalez14
Yak Posting Veteran
73 Posts |
Posted - 2008-12-01 : 10:27:40
|
| also if i put distinct it only returns 15. Why? |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-12-01 : 10:34:26
|
| because somewhere you have 1-N relationship.if you have a parent row in one table and 2 child rows in other table, an inner join between them will return 2 rows.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
jgonzalez14
Yak Posting Veteran
73 Posts |
Posted - 2008-12-01 : 11:05:26
|
| I just saw that i have one patron who has checked out more that one book. Thanks! :) |
|
|
|
|
|
|
|
|
Database design