Count on Inner Join Tables

[Meezee]

Hi all....

Have a bit of a dilema involving getting a count on an inner join table select.

The projects (WU_Title) are listed in one table (BPI_Upload) and the second table (BPI_ProjectFeedback) holds multiple rows that are added via form where PF_Project is the same as WU_Title.

Here is the current select I am using...
---------------------------------------

SELECT t1.FileID, t1.WU_Title, t1.WU_Start, t1.WU_End, t1.WU_ProjectStatus, t2.PF_Project
FROM dbo.BPI_Upload t1 INNER JOIN dbo.BPI_ProjectFeedback t2
ON t1.WU_Title = t2.PF_Project
WHERE t1.WU_ProjectStatus = 'Completed'

--------------------------------------

This presently returns:
----------------------------------
ID(1)Project(t1) ID(2) Project(t2)
1 project_a 1 project_a
1 project_a 2 project_a
1 project_a 3 project_a
---------------------------------

What I need is to return only the title and a count from the second table of how many times the title occurs there.

Ultimately ....
Title[project_a] Count[3]

I am having difficulty with where to place the COUNT() within the select.

Any suggestions would be appreciated.

Thanks...

[MAK]

Select WU_Title,count(WU_Title) from
(
SELECT t1.FileID, t1.WU_Title, t1.WU_Start, t1.WU_End, t1.WU_ProjectStatus, t2.PF_Project
FROM dbo.BPI_Upload t1 INNER JOIN dbo.BPI_ProjectFeedback t2
ON t1.WU_Title = t2.PF_Project
WHERE t1.WU_ProjectStatus = 'Completed'
) as mytable group by WU_Title

[Meezee]

Many thanks...

If you can't put the count inside the join ... put the join inside the count.

Thanks again....

[singh]

Your query may be as simple as this one...

SELECT t2.PF_Project, count(t2.PF_Project)
FROM dbo.BPI_Upload t1 INNER JOIN dbo.BPI_ProjectFeedback t2
ON t1.WU_Title = t2.PF_Project
group by t2.PF_Project

[MAK]

Singh.

you forgot the condition
t1.WU_ProjectStatus = 'Completed'

[singh]

Mak,
Oh! yeah... Thanks...
I forgot to put the condition.
I am just wondering... will it work?

[Meezee]

Yes and no....

It does indeed return the Title and the number of rows affected ... however ... if the Title from t1 has no current match in t2, nothing is output for the record from t1.

I can get around this partially by using LEFT JOIN which then returns the Title from t1 .... but it also counts it as a row and returns "1" (due to the join). It returns the correct t2 row value though.

So basically if the table information was:
t1.Title (Title from 1st table)
t2.Project[1] (an entry that matches 2nd table)
t2.Project[2] (Another entry that matches on the second table)
t2.Project[3] (Another entry that matches on the second table)
... it would return Title, 3 ...

however if the table information was:
t1.Title (Title from the 1st table)
t2.Project ( no entries )
... the WU_Title would not show up using INNER JOIN and would show up as "1" using LEFT JOIN ....

Does that make any sense??

[Meezee]

I found the solution
---------------------------------

SELECT WU_Title, WU_Start, WU_End, count(PF_Project)AS ReplyCount from

(SELECT t1.FileID, t1.WU_Title, t1.WU_Start, t1.WU_End, t1.WU_ProjectStatus, t2.PF_Project
FROM dbo.BPI_Upload t1 LEFT JOIN dbo.BPI_ProjectFeedback t2
ON t1.WU_Title = t2.PF_Project
WHERE t1.WU_ProjectStatus = 'Completed')

as mytable group by WU_Title, WU_Start, WU_End, PF_Project
ORDER BY WU_End DESC

-------------------------------------
Using the LEFT JOIN instead of INNER JOIN and setting the query to pick up instances of COUNT(PF_Projects) from table 2 instead of instances of the left table WU_Title ..... This will return "0" for any rows in table 2 that do not have matching entries.
--------------------------------
WU_Title[Title]- from the left join - recognizes and includes the Title
PF_Project[0]- instances from table 2 that match the Title from the 1st table
--------------------------------

Sounds easy enough ... but took a bit of work to figure out.