Editing to make more clear...
To summarize in english.
Each client will have multiple people assigned to it, of which I need to display a few.
Each client will have multiple engagements. We are interested in the newest of each distinct engagement (of which there are like 5 types).
Each row in the gridview will represent an engagement that belongs to a client that the user looking at it is part of.
Some things in the row are client level (so will be the same in each row) some are engagement level (so will be different in each row).



Problems:
If there are no employees for a client with position Associate or Lead Verifier, the row doesn't return at all. Need to fix this.

Things still left to do:
Each C.Name can have multiple E.Classifications.
I want to grab the most recent of each Identical E.Classification.

Example.
Class 1 - 4q 2008
Class 2 - 4q 2008
Class 3 - 4q 2008
Class 3 - 1q 2007
Class 1 - 1q 2009
This should return three rows.
Class 1 - 1q 2009 (most recent)
Class 2 - 4q 2008 (Only one)
Class 3 - 4q 2008 (most recent)

To do this I'd need to grab max YEAR and then max QTR (since number is first it will grab 4q as highest)

For each of these rows I need the following: E.Classification,
E.End_Quarter, E.End_Year, E.Data_In, E.Pts_Sent, E.RFD_Sent, E.RR_Assigned, E.Pending_C_Response,
datediff(d, getdate(), e.data_in) as Days_Pending, E.Memo

Finally I need to find the max() of a date from another table Touch_Points - this date is applied at the client level so will be the same for each engagement.

Any help with existing problem or adding in additional parts mentioned would be greatly appreciated.


This post has been edited by gymratz: 29 Sep, 2008 - 03:34 PM

Change your JOIN type. An INNER JOIN will only return results if there's a match in both tables. A LEFT OUTER JOIN will return all the rows from the first table, even if there are no matches in the second table. A RIGHT OUTER JOIN will return all the rows from the second table, even if there are no matches in the first table.

Here's a pretty good article on JOINS In MSSQL Hope that helps

Also, I'm moving this to the ASP.NET forum

I tried using LEFT OUTER JOIN as that is the one that I thought I needed. However it has the opposite problem of returning too many results.
Let me try to explain.

There are only 3 records total so the max rows I should ever see are 3.
With INNER JOIN I only see 2 because one row doesn't have one of those last two fields (Associate or Lead Verifier).
If I change this to OUTER LEFT JOIN I get 41 rows.
It's like it's taking the left outer of that specific part
LEFT OUTER JOIN Employees as EmpA on EmpA.ID = RCA.Emp_ID AND RCA.Position = 'Associate'

But what I really want to do is left outer join that inner join with the previous joins... Does that make any sense?



That is probably more accurately describing what I am trying to do - but obviously doesn't work (that just throws an error).

Did you try a RIGHT OUTER JOIN?

That returns 48 rows, lol.
Changing from inner, to outer left or outer right seems to affect the EmpA.ID = RCA.Emp_ID - but does not seem to affect the way that the join as a whole gets joined with the others in the query.

First, sanity check, the count should be no more than


On second thought, no more that this:


With that in mind, we start here:



Now add in one of your names. Let's use a subquery


There is a fundamental assumption here, there we're only going to get one employee per clientId. If that's not the case, you'll get more than you want.

If this looks good, then add in the other two. If it's not working, analyize the sub queries on their own. e.g.


CountA should equal CountB. If it doesn't, then assumptions about you data may be off.

Hope this helps.

Actually the only time I was counting anything was just to show that it wasn't returning how many records I knew where there.

Here is my new code thanks to you.


Thise code actually satisfies almost all of what I need. I realized that some of the things I was asking for aren't needed due to policy (no two engagements of the same name can be going at the same time, thus only one should have a reports_out as NULL)

One last thing I need to add.
I need to add the following and hoping to get one more example to help teach me why I can't figure this out on my own.

LEFT OUTER JOIN (Select MAX(DATE) from Onsite_Testing_Visits) WHERE Onsite_Testing_Visits.Client_ID = (client ID of the engagement)

I'm trying to add that in there. This is related to the client level. Does this make enough sense that you can help one more time?

Thanks much!

Perhaps something like this: