closer, but still didnt quite work.
still only returns articles that actually have pages. i wanted to display
articles with a "pageCount" of 0, plus i still got the error about not being
able to group by bit fields (articleActive is a bit field, i guess i could
change to smallint or something).
thanks for pointing me in the right direction though.
> try this
>
>
> SELECT art.articleID, art.articleName, art.articleActive, aut.authorName,
> count(p.articleID) as pageCount
> FROM articles art, authors aut, articlePages p
> WHERE art.authorID = aut.authorID AND
> art.articleID = p.articleID
> GROUP BY p.articleID, art.articleName, art.articleActive, aut.authorName
>
>
> Bryan Love ACP
> Internet Application Developer
> [EMAIL PROTECTED]
>
>
>
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, May 29, 2001 1:55 PM
> To: CF-Talk
> Subject: SQL Question...
>
>
> ok, this is probably a simple question, but i'm hoping somebody out there
> can help me.
>
> i have a database of articles and i am trying to join the article table
with
> the author table, but i also have a table that has all the individual
pages
> of an article (articles can be mulitple pages long). i am trying to write
a
> query that will get all the articles in the table joined with all the
author
> names as well as the number of pages that the article is (assuming that
> somebody has added a page to the article already).
>
> so ideally i want to display the following in my results:
>
> article name author name number of pages article active
> article 1 author A 3 YES
> article 2 author B 9 YES
> article 3 author B 0 NO
>
>
> here are my tables/columns that i have so far:
>
> ARTICLES
> ArticleID
> AuthorID
> ArticleName
> ArticleDateTime
> ArticleActive
>
> ARTICLEPAGES
> ArticlePageID
> ArticleID
> ArticlePageOrderID
> ArticlePageText
> ArticlePageName
>
> AUTHORS
> AuthorID
> AuthorName
> AuthorEmail
> AuthorActive
>
>
> and this is the query that i have so far.
>
> <cfquery name="getarticles" datasource="#datasource#">
> SELECT ar.articleid, ar.articlename, au.authorfirstname,
au.authorlastname,
> count(articlepageID) as numpages, ar.articleactive
> FROM articles ar, authors au, articlepages ap
> WHERE ar.authorID = AU.authorID
> AND ar.articleid = ap.articleid
> GROUP BY ar.articleid, ar.articlename, au.authorfirstname,
> au.authorlastname, ar.articledatetime, ar.articleactive
> ORDER BY articledateTime desc
> </cfquery>
>
> the obvious error right away is that it doesnt show articles with zero
pages
> because of my join. also this query is giving me an error (using SQL
server
> 7) because apparently you cant group on BIT fields.
>
> sorry to write a long question and monopolize bandwidth but i couldnt see
> this question answered in any of my saved CF-Talk emails
>
> peter
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
