Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 6th, 2005, 11:32 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default Help to expand a working query

Good day, I have a query that is working however I am having trouble getting one more value in my recordset. My objective here is to pull the required info in one query. It is a page currently getting five records, I would like to get a sixth. The two tables being addressed have the following structure (SQL Server 2000):

1..ForumCategorys: (PK=id) id,perId.posId,created,fOrder,catName,catDescripti on,inactive

2..ForumThreads:(PK=id, FK=catId)
id,perId,posId,projId,catId,datePosted,topicTitle, topicParent,thread

The following query works, it pulls five records and is pasted in here from query analyzer:
SELECT ForumCategorys.id,ForumCategorys.catName,ForumCate gorys.catDescription,
COUNT(ForumThreads.id) AS totalThreads,
MAX(ForumThreads.datePosted) AS Posted
FROM ForumCategorys,ForumThreads
WHERE ForumCategorys.inactive=0
AND ForumThreads.ProjID =9
AND ForumThreads.CatID = ForumCategorys.ID
GROUP BY ForumCategorys.id,ForumCategorys.catName,ForumCate gorys.catDescription
ORDER BY ForumCategorys.catName

I would like to get the posId (positionId) of the most recent post. The most recent post being MAX(ForumThreads.datePosted) as seen in the above query.

TYIA


Wind is your friend
Matt
__________________
Wind is your friend
Matt
Reply With Quote
  #2 (permalink)  
Old February 7th, 2005, 06:36 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi Mat,

to get that extra field in there you need to apply either of the techniques in this recent post http://p2p.wrox.com/topic.asp?TOPIC_ID=25561 to the ForumThreads table and then join that subquery to the ForumCategorys table.

hth
Phil
Reply With Quote
  #3 (permalink)  
Old February 8th, 2005, 02:35 AM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

pgtips

Thank you for your time, I got it working using:
SELECT ForumCategorys.id,ForumCategorys.catName,ForumCate gorys.catDescription, COUNT(ForumThreads.id),MAX(ForumThreads.datePosted ),position.shortTitle FROM ForumCategorys,ForumThreads,position WHERE ForumCategorys.inactive=0 AND ForumThreads.ProjID =9 AND ForumThreads.CatID = ForumCategorys.ID AND ForumThreads.PosId = Position.posID GROUP BY ForumCategorys.id,ForumCategorys.catName,ForumCate gorys.catDescription,position.shortTitle ORDER BY ForumCategorys.catName;

However there is a problem, if there is no entries in the ForumThreads table using this query it fails to show categories that have no topics. Even if there are no topics it should show the categroy and state there are 0 topics. Anyhow I have used two queries and reached my objective. A I said, trying to minimize the amount of queries was my aim.

TYVM

Wind is your friend
Matt
Reply With Quote
  #4 (permalink)  
Old February 8th, 2005, 08:02 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

use a LEFT OUTER JOIN instead of putting the join in the where clause
Reply With Quote
  #5 (permalink)  
Old February 9th, 2005, 01:40 AM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

aaggghhh I see. I am on a different machine at the moment but will try it tommorow. Thanking you kindly.

Wind is your friend
Matt
Reply With Quote
  #6 (permalink)  
Old February 9th, 2005, 08:17 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

I changed it a couple of different ways however still no record for a category that has not topics/threads under it (entries in the ForumThreads tbl) You were suggesting using a the LEFT OUTER JOIN in the following fashion?

SELECT ForumCategorys.id,ForumCategorys.catName,ForumCate gorys.catDescription, COUNT(ForumThreads.id),MAX(ForumThreads.datePosted ),position.shortTitle FROM ForumThreads LEFT OUTER JOIN ForumCategorys ON ForumCategorys.id = ForumThreads.catId LEFT OUTER JOIN Position ON position.posId = ForumThreads.posId WHERE ForumCategorys.inactive=0 AND ForumThreads.ProjID =9 GROUP BY ForumCategorys.id,ForumCategorys.catName,ForumCate gorys.catDescription,position.shortTitle ORDER BY ForumCategorys.catName;



Wind is your friend
Matt
Reply With Quote
  #7 (permalink)  
Old February 10th, 2005, 05:10 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

when you use a left or right join it matters which way around your tables are.

So instead of:
FROM ForumThreads LEFT OUTER JOIN ForumCategorys ON ForumCategorys.id = ForumThreads.catId

use:
FROM ForumCategorys LEFT OUTER JOIN ForumThreads ON ForumCategorys.id =
ForumThreads.catId

rgds
Phil
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Update Query Not Working Odifius BOOK: Beginning Access VBA 0 September 20th, 2006 11:33 AM
Delete query not working Mitch PHP Databases 4 January 4th, 2006 08:00 AM
UPDATE query is not working Sujit Joshi Beginning PHP 1 July 6th, 2005 04:07 PM
Delete Query not working kbonney98 Access 1 April 14th, 2005 06:29 AM
Query String not working!!!!!!! cmiller Beginning PHP 1 December 16th, 2003 12:30 PM



All times are GMT -4. The time now is 01:44 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.