| Author |
Topic |
|
dritzthevampyr
Starting Member
5 Posts |
 Posted - 2009-10-07 : 20:34:52
|
| Hello fellow SQL users, I am trying to work on my SQL database that I've set up that is supposed to be exampling a library system.Library(library#, library_name, library_address) Reader(reader#, reader name, reader type, reader address)Document(document#, document_title, document_type, document_descriptors)Copy(document# ,copy#, library#, copy_location, copy_status)copy status can be 'available'or 'borrowed'Borrowing(reader#, document#, copy#, date_borrowed, date_returned)I'm supposed to put through a query that returns the document that is the most borrowed. I would have to compare the copy#'s to see what document_title has the most copy# that have a copy_status of 'borrowed'. How exactly would I do this in SQL? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-10-07 : 21:59:34
|
[code]select d.[document#], d.[document_title], count(*)from Document d inner join Copy c on d.[document#] = c.[document#] inner join Browsing b on d.[document#] = b.[document#] and c.[copy#] b.[copy#]where c.[copy_status] = 'borrowed'group by d.[document#], d.[document_title]order by count(*) desc[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
dritzthevampyr
Starting Member
5 Posts |
Posted - 2009-10-07 : 22:04:24
|
quote:
Originally posted by khtan
select d.[document#], d.[document_title], count(*)from Document d inner join Copy c on d.[document#] = c.[document#] inner join Browsing b on d.[document#] = b.[document#] and c.[copy#] b.[copy#]where c.[copy_status] = 'borrowed'group by d.[document#], d.[document_title]order by count(*) desc
KH[spoiler]Time is always against us[/spoiler]
I see your query is supposed to show the descending order from most borrowed to least borrowed.The query that I need requires just posting what is the most borrowed out of all the other copies. Example of this: if book A has 3 copies that are borrowed and book B has 2 copies that are borrowed, then only book A should be returned from the query since it has the most borrowed. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-10-07 : 22:33:38
|
are you using SQL 2005 / 2008 ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
dritzthevampyr
Starting Member
5 Posts |
Posted - 2009-10-07 : 22:51:28
|
| I'm using MySQL 5.0...not sure what year it is. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-10-07 : 22:57:12
|
quote:
Originally posted by dritzthevampyr
I'm using MySQL 5.0...not sure what year it is.
than you are totally way out. This is a Microsoft SQL Server forum. for MySQL, try dbforums.com or mysql.com
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-10-07 : 22:58:38
|
i think MySQL has a syntax to just retrieve one record . . somthing like LIMIT 1try adding that in. It should give you the record with most borrowed document
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
dritzthevampyr
Starting Member
5 Posts |
Posted - 2009-10-07 : 23:05:03
|
quote:
Originally posted by khtan
quote:
Originally posted by dritzthevampyr
I'm using MySQL 5.0...not sure what year it is.
than you are totally way out. This is a Microsoft SQL Server forum. for MySQL, try dbforums.com or mysql.com
KH[spoiler]Time is always against us[/spoiler]
Thanks anyway |
|
|
|
dritzthevampyr
Starting Member
5 Posts |
Posted - 2009-10-07 : 23:10:27
|
quote:
Originally posted by khtan
i think MySQL has a syntax to just retrieve one record . . somthing like LIMIT 1try adding that in. It should give you the record with most borrowed document
KH[spoiler]Time is always against us[/spoiler]
Even if I limit it, say for instance the records had two books that had the same number of copies that were being borrowed. The limit would only generate the first book, not the second one. Isn't there a way around that? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-10-07 : 23:43:33
|
| Gotta ask the MySQL wizards about that. DBForums is your best bet. |
|
|
|
|
Finding a most occuring item (help)