Page 1 of 1

problem with query

Posted: Fri Aug 04, 2006 4:14 am
by kreoton
Hi,

First of all i want to sorry of my english. Now i will explain what problem do i have.
I have 2 tables, in first table i write book code and this book copy codes, like this:

books table:

Code: Select all

book_id
book_copy_id
other info...
data looks laike this

Code: Select all

book_id | book_copy_id
1 | 100
1 | 101
1 | 102
2 | 103
2 | 104
etc..
In second table goes info about book orders:
orders table:

Code: Select all

order_id 
user_id 
book_id 
staus
in staus field can be 3 posibilities like: rent ordered and in_store

my problem is: i whant to count how many users wants one book and count how many copys i have (hope you understand me :roll: ) how should i write a query/querys to have theys numbers?

Posted: Fri Aug 04, 2006 4:41 am
by GM
You need to do something like:

Code: Select all

SELECT a.book_id, a.tot_copies, b.tot_users FROM

   (SELECT book_id, count(book_copy_id) as tot_copies 
    FROM books GROUP BY book_id ORDER BY book_id) a,

   (SELECT book_id, count(user_id) as tot_users 
    FROM orders GROUP BY book_id ORDER BY book_id) b

WHERE a.book_id = b.book_id
This may not be completely correct, but it should give you the basis for doing what you need.

Posted: Fri Aug 04, 2006 5:00 am
by kreoton
thanks this very much, it realy helped me :wink:

Posted: Wed Aug 09, 2006 4:27 am
by kreoton
Sloved
i got one more question, how get results in time interval in eg. past week results?