Query shows 1 less than what's available in the #temp table

Question

So let's say #temp has 4 rows, the output will be 3(should be 4), if it has 1, then output will be 0.

I'm not quite sure what's going on. Wondering if anyone can tell by looking at the query.

SELECT TH.TnnNumber,
    CASE WHEN COUNT(DISTINCT TL.DiscountCodeID) > 1 THEN 'Varies, View Tnn' ELSE CAST(MAX(DC.Value) AS NVARCHAR(50)) END AS Discount,
    CASE WHEN TS.SpinID > 4 THEN 'Has Specifics, View Tnn' ELSE TS.Value END AS Spin,
    CASE WHEN COUNT(DISTINCT TL.Commission_HMM) > 1 THEN 'Varies, View Tnn' ELSE CAST(MAX(ISNULL(str(TL.Commission_HMM, 12), 'Default Comm')) AS NVARCHAR(50)) END AS Commission_HMM,
    CASE WHEN COUNT(DISTINCT TL.Commission) > 1 THEN 'Varies, View Tnn' ELSE CAST(MAX(ISNULL(str(TL.Commission, 12), 'Default Comm')) AS NVARCHAR(50)) END AS Commission,
    TL.TnnID
FROM [DBSERV].Tnn.DBO.Tnn_Header AS TH
LEFT JOIN [DBSERV].Tnn.DBO.Tnn_LINE AS TL
    ON TH.TnnID = TL.TnnID
LEFT JOIN [DBSERV].Tnn.DBO.Tnn_Spin AS TS
    ON TH.SpinID = TS.SpinID
LEFT JOIN [DBSERV].Tnn.DBO.Tnn_DiscountCode AS DC
    ON TL.DiscountCodeID = DC.DiscountCodeID
INNER JOIN #temp AS T
    ON T.Tnn = TH.TnnNumber
GROUP BY TH.TnnNumber,
    TS.SpinID,
    TS.Value,
    TL.TnnID

Required output:

+-----------+----------+-------------------------+----------------+--------------+-------+
| TnnNumber | Discount |          Spin           | Commission_HMM |  Commission  | TnnID |
+-----------+----------+-------------------------+----------------+--------------+-------+
|    902054 | 50-20-9  | Has Specifics, View Tnn | Default Comm   | Default Comm |  5855 |
|    907616 | 50-20-20 | Half                    | Default Comm   |            2 |  6111 |
|    910019 | 50-20-9  | Half                    | Default Comm   | Default Comm |  7015 |
|    915919 | 50-20-9  | Half                    | Default Comm   | Default Comm |  7015 |
+-----------+----------+-------------------------+----------------+--------------+-------+

Actual output:

+-----------+----------+-------------------------+----------------+--------------+-------+
| TnnNumber | Discount |          Spin           | Commission_HMM |  Commission  | TnnID |
+-----------+----------+-------------------------+----------------+--------------+-------+
|    902054 | 50-20-9  | Has Specifics, View Tnn | Default Comm   | Default Comm |  5855 |
|    907616 | 50-20-20 | Half                    | Default Comm   |            2 |  6111 |
|    910019 | 50-20-9  | Half                    | Default Comm   | Default Comm |  7015 |
+-----------+----------+-------------------------+----------------+--------------+-------+

Values in #temp

Right join:

+-----------+----------+-------------------------+----------------+--------------+-------+
| TnnNumber | Discount |          Spin           | Commission_HMM |  Commission  | TnnID |
+-----------+----------+-------------------------+----------------+--------------+-------+
| NULL      | NULL     | NULL                    | Default Comm   | Default Comm | NULL  |
| 902054    | 50-20-9  | Has Specifics, View Tnn | Default Comm   | Default Comm | 5855  |
| 907616    | 50-20-20 | Half                    | Default Comm   |            2 | 6111  |
| 910019    | 50-20-9  | Half                    | Default Comm   | Default Comm | 7015  |
+-----------+----------+-------------------------+----------------+--------------+-------+

Post source data, required and actual output. Script is fine, I like it. — Ivan Starostin, Mar 02 '16 at 18:43
They are unique for sure, also if #temp has 1 value.. then the output is 0.. so it's skipping the last record for some reason. — sojim2, Mar 02 '16 at 18:50
You could use http://www.sensefulsolutions.com/2010/10/format-text-as-table.html to format your tables — Juan Carlos Oropeza, Mar 02 '16 at 18:51
Without the source data we can only guess. Read [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) You also can post a working sample using http://sqlfiddle.com/ — Juan Carlos Oropeza, Mar 02 '16 at 18:53
If I use cursor as specified in this post, it correctly outputs 4 records: http://stackoverflow.com/questions/35470618/sql-server-how-to-output-one-table-result-from-multiple-results-with-a-while-que — sojim2, Mar 02 '16 at 18:57
Looks like the problem is `TH` doesnt have the same rows as `#TEMP` and `INNER JOIN` is removing one row. Try with `RIGHT JOIN #temp AS T` and let me know the output — Juan Carlos Oropeza, Mar 02 '16 at 18:59
Updated the result with 4 records this time, but it's showing the last `TnnNumber` as `NULL` — sojim2, Mar 02 '16 at 19:05
`LEFT JOIN` resulted in 4531 rows, all the Tnn number are unique too.. strange? — sojim2, Mar 02 '16 at 19:09
Actually, I think @JuanCarlosOropeza was correct about the `RIGHT JOIN` now that I look at the query again. BUT, you need to change your SELECT to be from `T.Tnn` instead of `TH.TnnNumber`. — Solomon Rutzky, Mar 02 '16 at 19:15
I assume `TnnID` is unique but two different `TnnNumber` must link the same row with `TnnID=7015` - how is that? — Ivan Starostin, Mar 02 '16 at 19:18

score 2 · Accepted Answer · answered Mar 02 '16 at 19:23

2

As your RIGHT JOIN query show your

[DBSERV].Tnn.DBO.Tnn_Header AS TH

Doesn't have the row with ID = 915919 that is why RIGHT JOIN return NULL and INNER JOIN return a row less

try

SELECT *
FROM [DBSERV].Tnn.DBO.Tnn_Header AS TH
WHERE TH.TnnNumber = 915919

Now you have to check how are you creating #temp and validate you are using the same ID related to Tnn_Header

answered Mar 02 '16 at 19:23

Juan Carlos Oropeza

47,252
12
78
118

You are correct, I was assuming the cursor output was correct, but it isn't. We are in a development sql server so some data might be missing. – sojim2 Mar 02 '16 at 19:34

Query shows 1 less than what's available in the #temp table

1 Answers1

Linked