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
