agregate Count return all rows, Left Jin 1

[SPrelewicz]

I have 2 tables on a lyric discussion site

Titles
title_id
title_name
title_lyricist
title_lyrics
title_artist
title_entered

And POSTS
post_id
title_id
post_author
post_text

I want to be able to list all the titles, and count the # of posts for each title. The problem is some titles have zero posts so my query ignores thos titles, butI syill want them returnd where Ill add a ')' for count.

This is the best i could come up with, but still only those that have posts are returned. I still want the titles that don't have a match in the posts table to be returned with a total_count of zero.

Any any help on this ASAP would be much appreciated.


<a href='

http://www.flyingroman.com'>Flying

Roman lyric meanings</a>

 

[r937]

this is a classic situation for a left outer join

select title_id
     , title_name       
     , title_lyricist       
     , title_lyrics       
     , title_artist       
     , title_entered  
     , count(post_id) as posts
  from Titles
left outer
  join POSTS
    on Titles.title_id
     = POSTS.title_id    
group
    by title_id
     , title_name       
     , title_lyricist       
     , title_lyrics       
     , title_artist       
     , title_entered

rudy

http://r937.com/

SQL Consulting

 

[SPrelewicz]

Like magic. it's OUTER joins I just cant seem to grasp, but hopefully this example will engrave in my memory.

Thanks alot!

And for posting on 2 bords

Scott

 

[SPrelewicz]

Okay, this all worked great except for the title_id. the table returns 2 title_id fields, 1 from each table. In my Perl, when I process each record, it gives me the post title_id which is null. How would I structure the query so I gotthe title_id from titles, or only the title_id from titles was returned?

Thanks again.

http://www.flyingroman.com/_about_me

About me at Flying Roman.com lyric meaning and discussion

http://www.scottprelewicz.comScott

Prelewic, Renassance Man

 

[r937]

oh yeah, i shoulda caught that, sorry

change the first line to

select Titles.title_id

rudy

http://r937.com/

SQL Consulting

 

[SPrelewicz]

Thank you again. I usually post mostly on the Perl board, but I'll be here often thanks to the fine answers and discussions on this board.

http://www.flyingroman.com/_about_me

About me at Flying Roman.com lyric meaning and discussion

http://www.scottprelewicz.comScott

Prelewic, Renassance Man