Left Join returning less rows

I have a dataset from a complex query which I've selected into a temp table. Let's call that #mydata

MyData is essentially a list of Client Transactions. Each client could have done multiple transaction开发者_JAVA百科s

i.e.

ClientId    TransactionId

1           123
1           234
1           564
2           897
3           714
5           850
5           963
7           325
8           912
8           375
8           640

Now for each row in this table I want to lookup a value from another table by joining on the client id.

The other table contains a rating for each client. And there can only be 1 "approved" rating per client. But there could be other ratings for the client in a non approved state. And it could also be that there is not yet a rating for this client. (The approved status id is 5 - see below).

So I am using a LEFT join from mydataset to ClientRating

I want to end up with the same data that is in #mydata, just with an extra column. I want the same number of rows as #mydata. If there is an approved client rating then put it in the extra column, if there is not, leave it blank

Everything I've tried so far doesn't work

What am I doing wrong?

Without a join I get 2050 rows. With all the joins I've tried I get a different number:

--this returns 2050 rows
select  * 
from    #mydata md

 --this returns 2111 rows
    select  * 
    from    #mydata md
    LEFT JOIN   ClientRating b on b.ClientId = md.ClientId AND (ClientRatingStatusid = 5)
--this returns 2111 rows
select  * 
from    #mydata md
LEFT JOIN   ClientRating b on b.ClientId = md.ClientId AND (ClientRatingStatusid = 5 OR ClientRatingStatusid IS NULL)

--this returns 2111 rows
select  * 
from    #mydata md
LEFT outer JOIN ClientRating b on b.ClientId = md.ClientId AND (ClientRatingStatusid = 5 OR ClientRatingStatusid IS NULL)

--this returns 2099 rows
select  * 
from    #mydata md
LEFT  JOIN  ClientRating b on b.ClientId = md.ClientId 
Where  (ClientRatingStatusid = 5 OR ClientRatingStatusid IS NULL)

You have essentially three options.

Option 1 - Limit the Joined Result Set

You can JOIN on a subquery to limit the returns per client:

select  * 
from    #mydata md
LEFT JOIN  (SELECT clientId, 
           MAX(otherfield) as otherfield, 
           MAX(otherfield2) as otherfield2
           FROM ClientRating
           GROUP BY ClientId) b 
   on b.ClientId = md.ClientId 

Option 2 - Limit the final result Set

For this you will need to remove the SELECT * and specify fields. You will also need to GROUP BY your ID field:

 select  md.Clientid, 
         MAX(field1) as field1, 
         MAX(field2) as field2
 from    #mydata md
 LEFT JOIN   ClientRating b 
   on b.ClientId = md.ClientId 
 WHERE ClientRatingStatusid = 5
 GROUP BY md.cliendid

Option 3 - Remove the dupes in your JOINed table

If you fix the issues in your data then the original query will work.

As a side note, your second condition belongs in a WHERE clause not in the JOIN criteria.

The reason is simple: Your join condition matches multiple entries in ClientRating for one entry in #mydata.

If you only want the approved ClientRatingStatusid, then why are you also including the unapproved ones (NULL). If there is no rating of 5, then LEFT JOIN should return a NULL for your query. Does this work:

select  * 
from    #mydata md
LEFT JOIN   ClientRating b on b.ClientRating = md.ClientRating AND ClientRatingStatusid = 5

As there are multiple values of the same ClientId, each id will join with the values in the ClientRating table. One of the approaches can be to have a separate table where you only have Id, ClientId and the other table to have ClientId, TransactionId. Now you can make a join from the Id, ClientId table

Table1 
Id, ClientId

Table2
ClientId, TransactionId

I don't see a problem with it...

The parent table has 2050 records, and the child has more -- it's a one-to-many relationship.

The way you filter your final recordset is what results in the 2111 versus 2099. If you search your parent table for duplicate id's, I bet you find the problem.

SELECT * FROM parent HAVING count(id) > 1