counting records
Moderator: General Moderators
- Think Pink
- Forum Contributor
- Posts: 106
- Joined: Mon Aug 02, 2004 3:29 pm
counting records
hello, pls help.
i have 2 table
tbl_domains
id_domain
domain
tbl_books
id_book
book_name
code_domain
i have a place on my page, where all my domains are listed, but I don't know how i could write near the domain how manny books I have in that domain.
something like that
sf (15)
programming (135)
design (17)
thx in advance for any help.
i have 2 table
tbl_domains
id_domain
domain
tbl_books
id_book
book_name
code_domain
i have a place on my page, where all my domains are listed, but I don't know how i could write near the domain how manny books I have in that domain.
something like that
sf (15)
programming (135)
design (17)
thx in advance for any help.
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
- Think Pink
- Forum Contributor
- Posts: 106
- Joined: Mon Aug 02, 2004 3:29 pm
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
Why not inner join and use a WHERE clause?Phenom wrote:not quite sure, but try GROUP BY `domain`
EDIT | Forget that - you have no foreign key to use from what I can gather.
EDIT AGAIN | Is code_domain the foreign key for tbl_domain?
Code: Select all
"e;SELECT COUNT(*) FROM tbl_domains, tbl_books WHERE
tbl_domains.id_domain = tbl_books.code_domain AND
tbl_domains.domain = '$domain'"e;- Think Pink
- Forum Contributor
- Posts: 106
- Joined: Mon Aug 02, 2004 3:29 pm
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
Maybe off topic but...Think Pink wrote:i'm sorry, but i don't know what do you mean when you say foreign key.
You have two tables. Both relate to each other. Both have a primary key. Now you need a foriegn key surely to get the books for that domain.
Foreign key = a key which identifies a record in another table, typically the other table's primary key.
So lets say I have two tables, one is names and dates of births, the other is favourite colors.
people
person_id
color_id (our foreign key)
first_name
last_name
dob
colors
color_id
color_name
To get the favourite color of "John Smith" who's ID is 54:
Code: Select all
SELECT color_name FROM people, colors WHERE people.color_id=colors.color_id AND people.person_id='54'PHENOM| Queries should be in
Code: Select all
tags [/color][/size]- Think Pink
- Forum Contributor
- Posts: 106
- Joined: Mon Aug 02, 2004 3:29 pm
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
In which case the book count should be (where $domain_id is the primary key from tbl_domains):
Code: Select all
"e;SELECT COUNT(tbl_books.code_domain) AS total_books FROM tbl_domains AS d, tbl_books AS b WHERE d.id_domain=b.code_domain AND d.id_domain='$domain_id'"e;- Pyrite
- Forum Regular
- Posts: 769
- Joined: Tue Sep 23, 2003 11:07 pm
- Location: The Republic of Texas
- Contact:
I would use a GROUP BY with an INNER JOIN. Like this?
This will output like:
Code: Select all
SELECT
domain,
count(*) AS 'Number of Books'
FROM
tbl_domains AS d
INNER JOIN tbl_books AS b ON b.code_domain = d.id_domain
GROUP BY
domainCode: Select all
domain : Number of Books
-------:----------------
yahoo 37
google 29
amazon 6- Think Pink
- Forum Contributor
- Posts: 106
- Joined: Mon Aug 02, 2004 3:29 pm