[JohnRamon]

OK i have 2 tables that I need to join

SELECT bl.blID, bl.blTitle, bl.blDate, bl.blText, bl.blText2, bl.blCatergory, bl.blLink 
FROM blog_tbl bl		
ORDER BY bl.blID DESC

SELECT COUNT(*) as num
FROM comment_tbl
WHERE cbid = bl.blID 
AND cStatus = 1

I have the blog table that I'm pulling the posts from and the comment table I'm pulling the comment count. Right now I'm pulling the second query in a loop and it's running for evey post. I want to join the 2 so I only acces the database once and the count will be available on the loop not in it.

[psykoprogrammer]

Well, the sample I'm posting here will work only on databases that support subqueries.

SELECT 
   bl.blID, 
   bl.blTitle,
   bl.blDate,
   bl.blText,
   bl.blText2, 
   bl.blCatergory, 
   bl.blLink,
   (SELECT COUNT(ct.cbid) FROM comment_tbl ct WHERE ct.cbid=bl.ID AND ct.cStatus=1) AS commentCount
FROM blog_tbl bl		
ORDER BY bl.blID DESC

This post has been edited by psykoprogrammer: 31 October 2006 - 02:15 PM

[gregoryH]

JohnRamon, on 31 Oct, 2006 - 08:38 AM, said:

OK i have 2 tables that I need to join

SELECT bl.blID, bl.blTitle, bl.blDate, bl.blText, bl.blText2, bl.blCatergory, bl.blLink 
FROM blog_tbl bl		
ORDER BY bl.blID DESC

SELECT COUNT(*) as num
FROM comment_tbl
WHERE cbid = bl.blID 
AND cStatus = 1

I have the blog table that I'm pulling the posts from and the comment table I'm pulling the comment count. Right now I'm pulling the second query in a loop and it's running for evey post. I want to join the 2 so I only acces the database once and the count will be available on the loop not in it.

Hi

My understanding of your question is:

You want to show everything in table 1 and just the count of related material from table 2

First, you need to join the tables on the linking field and count(*) from table 2

Something like:

Select F.fld1,F.fld2, count(S.*)
from Table1 F, Table2 S
where S.fld = F.fld

[Skinnyarms]

gregoryH, on 2 Nov, 2006 - 01:43 AM, said:

JohnRamon, on 31 Oct, 2006 - 08:38 AM, said:

OK i have 2 tables that I need to join

SELECT bl.blID, bl.blTitle, bl.blDate, bl.blText, bl.blText2, bl.blCatergory, bl.blLink 
FROM blog_tbl bl		
ORDER BY bl.blID DESC

SELECT COUNT(*) as num
FROM comment_tbl
WHERE cbid = bl.blID 
AND cStatus = 1

I have the blog table that I'm pulling the posts from and the comment table I'm pulling the comment count. Right now I'm pulling the second query in a loop and it's running for evey post. I want to join the 2 so I only acces the database once and the count will be available on the loop not in it.

Hi

My understanding of your question is:

You want to show everything in table 1 and just the count of related material from table 2

First, you need to join the tables on the linking field and count(*) from table 2

Something like:

Select F.fld1,F.fld2, count(S.*)
from Table1 F, Table2 S
where S.fld = F.fld

In MS SQL (and probably others as well) you'd need a group by clause...like:

Select F.fld1,F.fld2, count(S.*)
from Table1 F, Table2 S
where S.fld = F.fld
GROUP BY F.fld1,F.fld2