Geek Answers Handbook

Left Join returns fewer rows

I have a dataset from a complex query that I selected in the temp table. Let me call it #mydata p>

MyData is essentially a list of customer transactions. Each client could make several transactions

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 find the value from another table by joining the client id.

The other table contains the rating for each client. And there can only be 1 “approved” rating for each client. But there may be other ratings for the client in an unapproved state. And it may also be that for this client there is no rating yet. (Approved status identifier 5 - see below).

So, I am using the LEFT connection from mydataset to ClientRating

I want to get the same data as in #mydata, only with an extra column. I need as many lines as #mydata. If there is an approved customer rating, then put it in an additional column; if not, leave it blank

Everything I've tried so far doesn't work.

What am I doing wrong?

Without a connection, I get 2050 rows. With all the connections I 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)
+4
source share
5 answers

You have essentially three options.

Option 1 - Limit the set of specified search results

You can JOIN in the subquery limit the return to the 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 Outcome Install

To do this, you need to delete SELECT * and specify the fields. You will also need 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 - Delete Duplicates in the JOIN ed Table

If you fix the problems in your data, the original request will work.

As a side note, your second condition does not apply to the WHERE in JOIN criteria.

+2
source

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

+1
source

If you want only an approved ClientRatingStatusid , then why do you also include Unapproved (NULL). If the rating is not 5, then LEFT JOIN should return NULL for your request. It works:

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

Because there are multiple values ​​for the same ClientId, each identifier is combined with the values ​​in the ClientRating table. One approach may be to have a separate table in which you have Id, ClientId , and the other table is ClientId, TransactionId . Now you can make a connection from the Id, ClientId table Id, ClientId

 Table1 Id, ClientId Table2 ClientId, TransactionId
0
source

I do not see a problem with this ...

There are 2050 entries in the parent table, and the child has more - this is a one-to-many relationship.

The way to filter your final recordset is the result of 2111 versus 2099. If you are looking for your parent table to duplicate the identifier, I am sure you will find a problem.

 SELECT * FROM parent HAVING count(id) > 1
0
source

More articles:


All Articles