Help with Join Query Please

Hi All,
I need help figuring out a query.
I have two tables:
REQ and PO
The schema here is that there can be multiple PO rows per a single REQ row.
example:
REQ PO
--
Requistion1
PO1
PO2
PO3
I am creating a query that counts the number of REQ ROWS per Person
and the number of NULL Columns in the Row, including the PO Columns.
My problem is that the [Total Requests] SUM includes the PO Records and I
don't wan that.
Below is my query. Any help would be greatly appreciated.
Thanks,
John.
/*--*/
SELECT Assignedto,
COUNT(*) AS [Total Requests],
PRStatus AS [Status],
SUM(CASE WHEN (Region Is Null) Then 1 Else 0 End) AS [Region],
SUM(CASE WHEN (OrderList Is Null) Then 1 Else 0 End) AS [OrderList],
SUM(CASE WHEN (ClientName Is Null) Then 1 Else 0 End) AS [Client Name],
SUM(CASE WHEN (PRStatus Is Null) Then 1 Else 0 End) AS [PRStatus ],
/*---*/
/*PO Data*/
SUM(CASE WHEN (P.Supplier Is Null) Then 1 Else 0 End) AS [Supplier],
SUM(CASE WHEN (P.PONumber Is Null) Then 1 Else 0 End) AS [PO Number],
SUM(CASE WHEN (P.DatePOCreated Is Null) Then 1 Else 0 End) AS [Date PO
Created],
SUM(CASE WHEN (P.DatePOAnticipatedShip Is Null) Then 1 Else 0 End) AS [Date
PO Anticipated Ship],
/*---*/
FROM INTReq R
LEFT OUTER JOIN INTReq_PO P ON R.RID = P.RID
WHERE (PRStatus <> 'Cancelled')
GROUP BY Assignedto, PRStatus
Order By Assignedto, PRStatusJohn wrote:
> Hi All,
> I need help figuring out a query.
> I have two tables:
> REQ and PO
> The schema here is that there can be multiple PO rows per a single
> REQ row. example:
> REQ PO
> --
> Requistion1
> PO1
> PO2
> PO3
I think I understand what you mean, but you should eliminate all ambiguity
by following the recommendations here: www.aspfaq.com/5006

> I am creating a query that counts the number of REQ ROWS per Person
> and the number of NULL Columns in the Row, including the PO Columns.
> My problem is that the [Total Requests] SUM includes the PO Records
> and I don't wan that.
> Below is my query. Any help would be greatly appreciated.
>
Now I'm really lost. Please provide DDL, sample data and desired results (in
tabular format)
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||TRY
count(distinct REQ) as 'TotalRequest'
"John" <jrugo@.patmedia.net> wrote in message
news:uvCTGe2zFHA.3408@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I need help figuring out a query.
> I have two tables:
> REQ and PO
> The schema here is that there can be multiple PO rows per a single REQ
> row.
> example:
> REQ PO
> --
> Requistion1
> PO1
> PO2
> PO3
> I am creating a query that counts the number of REQ ROWS per Person
> and the number of NULL Columns in the Row, including the PO Columns.
> My problem is that the [Total Requests] SUM includes the PO Records and I
> don't wan that.
> Below is my query. Any help would be greatly appreciated.
> Thanks,
> John.
> /*--*/
> SELECT Assignedto,
> COUNT(*) AS [Total Requests],
> PRStatus AS [Status],
> SUM(CASE WHEN (Region Is Null) Then 1 Else 0 End) AS [Region],
> SUM(CASE WHEN (OrderList Is Null) Then 1 Else 0 End) AS [OrderList],
> SUM(CASE WHEN (ClientName Is Null) Then 1 Else 0 End) AS [Client Name],
> SUM(CASE WHEN (PRStatus Is Null) Then 1 Else 0 End) AS [PRStatus ],
> /*---*/
> /*PO Data*/
> SUM(CASE WHEN (P.Supplier Is Null) Then 1 Else 0 End) AS [Supplier],
> SUM(CASE WHEN (P.PONumber Is Null) Then 1 Else 0 End) AS [PO Number],
> SUM(CASE WHEN (P.DatePOCreated Is Null) Then 1 Else 0 End) AS [Date PO
> Created],
> SUM(CASE WHEN (P.DatePOAnticipatedShip Is Null) Then 1 Else 0 End) AS
> [Date PO Anticipated Ship],
> /*---*/
> FROM INTReq R
> LEFT OUTER JOIN INTReq_PO P ON R.RID = P.RID
> WHERE (PRStatus <> 'Cancelled')
> GROUP BY Assignedto, PRStatus
> Order By Assignedto, PRStatus
>
>|||Yup, that did the trick :)
Thank you very much for you help.
John.
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:e3zsPK3zFHA.2912@.TK2MSFTNGP10.phx.gbl...
> TRY
> count(distinct REQ) as 'TotalRequest'
>
> "John" <jrugo@.patmedia.net> wrote in message
> news:uvCTGe2zFHA.3408@.TK2MSFTNGP09.phx.gbl...
>