Sub Query Help

[Rohan84]

Hi,

I have two queries that i need to perform simultaneously.

First, i need to grab all the information from one table:

SELECT *
FROM MainPage
WHERE Company = 1

But at the same time, for each of these records from the first query, i need to know how many times MainPageID appears in another table (Page). ie:

SELECT Count(*)
FROM Page
WHERE MainPageID = *Current MainPageID from first query*

My issue is that i can't have two result sets open at a time and the data that processes this information is currently too complex to rewrite it to store in arrays for later use.

Any help on how to get add this second query to the first?

[Vidhura]

You can use group by clause to achieve this...
Hope the following helps

Select MainPage.ID,<fields>,count(Page.MainPageID) from MainPage left join Page on MainPage.ID=Page.MainPageID
group by MainPage.ID,<fields>

[Rohan84]

Thank you, that helps.