Hi everyone,

 I have 3 tables

1. Users (UserID,UserName)
2. Threads (ThreadID, UserID, ThreadTitel, Date)
3. Posts (PostID, UserID, PostTitel, Date)

I want 1 table for use in (GridView) with the users and the # Threads and # Posts for that User
Like this:

UserName  #Threads  #Posts
Peter          2             7
John           2             0
Ellen          1             2

I tried all kinds of COUNTs, JOINs and GROUP BYs, but not got that result.
When I only use Users and Threads, or Users and Posts everything is fine.
But the 3 tables together gives me no good COUNTs.

Can you help ?

Many thanks

How about creating a function such that
for each individual users, map to #Thread and #Posted to get the number of
Thread and post that individual user has then display in Gridview?

Hope that help. 

putting the COUNTs in a function would help, but then I am no longer able to SORT in the Gridview because the result of the function in NOT Databound :-(

"When I only use Users and Threads, or Users and Posts everything is fine. "

You can join these two queries as subquerys to form a result you want. (You need to start with a LEFT JOIN from users table.). If you cannot get what you want, please post these queries here. Thanks.

These are the 2 queries:

SELECT U.Name, COUNT(P.PostID) AS TotalP
FROM  Users AS U
LEFT OUTER JOIN Posts AS P ON U.UserID = P.UserID
WHERE (U.UserID = @parUserID)
GROUP BY U.Name

SELECT U.Name, COUNT(T.ThreadID) AS TotalT
FROM  Users AS U
LEFT OUTER JOIN Threads AS T ON U.UserID = T.UserID
WHERE (U.UserID = @parUserID)
GROUP BY U.Name

please notice that COUNT(P.PostID) and COUNT(T.ThreadID) are just for counting the records,
this can be done better I asume.

SELECT t1.UserID, t1.Name, a.TotalP, b.TotalT FROM Users t1

LEFT JOIN (SELECT U1.UserID, COUNT(P.PostID)

AS TotalP FROM Users AS U1 JOIN Posts AS P ON U1.UserID = P.UserID

GROUP BY U1.UserID) a ON t1.UserID=a.UserID

LEFT JOIN (SELECT U2.UserID, COUNT(T.ThreadID) AS TotalT

FROM Users AS U2 JOIN Threads AS T ON U2.UserID = T.UserID

GROUP BY U2.UserID) b

On t1.UserID=b.UserID

WHERE t1.UserID =@parUserID

Hi Limno,

Almost there,

Please allow me to give a maybe better example.
I used the above to simplefy things, but that does not work
 because there are more parameters involved :-(

Lets say I have a few banners that are shown on different websites.

So
A. there is a table for banners

B. there is a table for websites (SiteID, SiteName, etc)

I hope this clears things up.

Best regards,

Bonaparte

SELECT  tNames.BannerName+'('+Convert(NVARCHAR,tNames.BannerID)+')' as 'BannerName (ID)',
tNames.SiteName+'('+Convert(NVARCHAR,tNames.SiteID)+')' as 'SiteName (ID)',
t0.NumOfImpressions, t0.NumOfSales  FROM (SELECT a.BannerID, a.SiteID, a.NumOfImpressions, ISNULL(b.NumOfSales,0) as NumOfSales  FROM (SELECT BannerID, SiteID, COUNT(*) AS NumOfImpressions
FROM  Impressions
GROUP BY BannerID, SiteID) a LEFT JOIN (SELECT BannerID, SiteID, Count(*) as NumOfSales
FROM  Sales
GROUP BY BannerID, SiteID) b ON  a.BannerID= b.BannerID AND a.SiteID=b.SiteID) t0
INNER JOIN (SELECT Sites.SiteID, Sites.SiteName, t.BannerID, t.BannerName
FROM  Sites CROSS JOIN
                   (SELECT BannerID, BannerName
                    FROM   Banners) AS t) tNames on tNames.BannerID= t0.BannerID AND tNames.SiteID=t0.SiteID
Order By tNames.BannerID,tNames.SiteID

Hi

I have same type of plorblem is there any solution? I have three table tblContact, tblCustomer and tblOrder

I have written the quiry like this.

FROM tblCustomers AS t1 LEFT OUTER JOIN

FROM tblCustomers AS U1 INNER JOIN

GROUP BY U1.Customer_Id) AS a ON t1.Customer_Id = a.Customer_Id LEFT OUTER JOIN

FROM tblCustomers AS U2 INNER JOIN

GROUP BY U2.Customer_Id, T.ordernumber, T.Issue, T.OrderType) AS b ON t1.Customer_Id = b.Customer_Id

WHERE (b.ordernumber = @OrderNumber) AND (b.Issue = @Issue) AND (b.OrderType = @OrderType)

I want to search customer name and number, total contact of that customer and total order of that customer according to order number issue and order type

but its giving wrong output totalconact =1 and TotalCircuits= 1 istead of  14 and 19 can u pls resolve problem............