3 Replies - 2612 Views - Last Post: 22 November 2006 - 03:09 PM Rate Topic: -----

#1 JohnRamon  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 27-September 06

Joining 2 queries

Post icon  Posted 31 October 2006 - 08:38 AM

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.

Is This A Good Question/Topic? 0
  • +

Replies To: Joining 2 queries

#2 psykoprogrammer  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 2
  • View blog
  • Posts: 72
  • Joined: 09-October 06

Re: Joining 2 queries

Posted 31 October 2006 - 02:14 PM

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

Was This Post Helpful? 0
  • +
  • -

#3 gregoryH  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 60
  • View blog
  • Posts: 656
  • Joined: 04-October 06

Re: Joining 2 queries

Posted 02 November 2006 - 01:43 AM

View PostJohnRamon, 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

Was This Post Helpful? 0
  • +
  • -

#4 Skinnyarms  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 18-November 06

Re: Joining 2 queries

Posted 22 November 2006 - 03:09 PM

View PostgregoryH, on 2 Nov, 2006 - 01:43 AM, said:

View PostJohnRamon, 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

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1