You've discovered what happens when you don't have data in the joined
tables: you get nothing back. Since you'll never know whether or not a
particular author will have any articles or whether or not those articles
will have any pages, I would split this into 3 queries.
The first query would retrieve the author info. The second query would grab
the article headers and the third would grab the pages of the article.
---mark
------------------------------------
Mark Warrick - Fusioneers.com
Email: [EMAIL PROTECTED]
Phone: 714-547-5386
http://www.fusioneers.com
http://www.warrick.net
====================================
> -----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
