problem with query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
kreoton
Forum Commoner
Posts: 42
Joined: Fri Mar 03, 2006 7:27 pm

problem with query

Post 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?
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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.
User avatar
kreoton
Forum Commoner
Posts: 42
Joined: Fri Mar 03, 2006 7:27 pm

Post by kreoton »

thanks this very much, it realy helped me :wink:
User avatar
kreoton
Forum Commoner
Posts: 42
Joined: Fri Mar 03, 2006 7:27 pm

Post by kreoton »

Sloved
i got one more question, how get results in time interval in eg. past week results?
Post Reply