|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
June 20th, 2005, 10:02 AM
|
|||
|
|||
|
Select Count problem involving 2 tables
Hi folks
I have the following two table set up: CATEGORIES ------------- CATID (primary key) CATNAME NEWS --------- CATID (foreign key matched to CATID above) NEWSID (foreign key from another table) What I am trying to do is select each category name from the first table and then show the number of associated news stories (Count(NEWSID)) from the second table. I am beginning to think this cannot be done using just one SQL statement because multiple rows are involved. Can anyone help please?
|
|
#2
June 20th, 2005, 10:21 AM
|
||||
|
||||
|
On Oracle 9 or above you can use something like:
Code:
SELECT c.catname, count(n.newsid) news_number FROM categories c INNER JOIN news n ON c.catid = n.catid;
__________________
My blog Tutorials about OSS databases, DBMonster ... Contribute to OSS Development, fill bug reports! Developer Shed eSupport Commented my.ini/my.cnf (ADD YOUR OWN CONFIG TRICK) An introduction to database normalization Natural or Surrogate key Custom ordering for your results Correlated and uncorrelated subqueries Don't turn your outer joins into inner joins Random data (with a bias)
|
|
#3
June 20th, 2005, 10:47 AM
|
|||
|
|||
|
Hmmm, that's a nice idea and is exactly what I'm looking for. Unfortunately it results in a ORA-00937 error when I try it. I'm a bit of a novice at writing SQL queries so would welcome any pointers. Database is Oracle 9.2
|
|
#4
June 20th, 2005, 10:58 AM
|
||||
|
||||
|
Dont' worry, just a stupid mistake by me, but the error message should have told you everything ....
SELECT c.catname, count(n.newsid) news_number FROM categories c INNER JOIN news n ON c.catid = n.catid GROUP BY c.catname;
|
|
| Viewing: Dev Shed Forums > Databases > Oracle Development > Select Count problem involving 2 tables |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
