Missing Record

Transact-SQL
1

I have this query and I know I need to use alias's but I thought I had it working good. Today I found a "SalesOrder" with two records of the same "itemcode" ex. It has two lines on the order with "itemcode" = IGR7-3XL LON. In my query the result is only producing one line with the IGR7-3XL LON. Is this a join issue?

image
image957×109 60.8 KB

Source records the second column shows the two records as unique

source
source1327×156 146 KB 

SELECT TOP (100) PERCENT dbo.SO_SalesOrderDetail.SalesOrderNo, dbo.SO_SalesOrderDetail.ItemCode, dbo.SO_SalesOrderDetail.QuantityOrdered, dbo.SO_SalesOrderDetail.QuantityBackordered, 
                  dbo.SO_SalesOrderDetail.UDF_QTY_PULLED, dbo.SO_SalesOrderDetail.UDF_QTY_RECEIVED, dbo.SO_SalesOrderDetail.UDF_PROCESSED_IC, dbo.SO_SalesOrderHeader.UDF_PROCESS_INCOMPLETE, 
                  dbo.SO_SalesOrderHeader.UDF_ALLOCATED, dbo.SO_SalesOrderDetail.WarehouseCode, dbo.SO_SalesOrderHeader.UDF_DATE_ALLOCATED
FROM     dbo.SO_SalesOrderDetail INNER JOIN
                  dbo.SO_SalesOrderHeader ON dbo.SO_SalesOrderDetail.SalesOrderNo = dbo.SO_SalesOrderHeader.SalesOrderNo
WHERE  (dbo.SO_SalesOrderHeader.OrderType = 'S' OR
                  dbo.SO_SalesOrderHeader.OrderType = 'B') AND (dbo.SO_SalesOrderDetail.ItemType = '1')
GROUP BY dbo.SO_SalesOrderDetail.ItemCode, dbo.SO_SalesOrderDetail.SalesOrderNo, dbo.SO_SalesOrderDetail.QuantityOrdered, dbo.SO_SalesOrderDetail.QuantityBackordered, dbo.SO_SalesOrderDetail.UDF_QTY_PULLED, 
                  dbo.SO_SalesOrderDetail.UDF_QTY_RECEIVED, dbo.SO_SalesOrderDetail.UDF_PROCESSED_IC, dbo.SO_SalesOrderHeader.UDF_PROCESS_INCOMPLETE, dbo.SO_SalesOrderHeader.UDF_ALLOCATED, 
                  dbo.SO_SalesOrderDetail.WarehouseCode, dbo.SO_SalesOrderHeader.UDF_DATE_ALLOCATED
HAVING (dbo.SO_SalesOrderDetail.WarehouseCode = '000')
ORDER BY dbo.SO_SalesOrderDetail.SalesOrderNo
2

You're GROUPing BY all of the same columns in your SELECT list, so all unique combinations will collapse into a single row for each. Since you didn't have aggregate functions it wouldn't be apparent that rows were combined.

This should present the results you're expecting. I also used aliases for tables:

SELECT d.SalesOrderNo
,d.ItemCode
,d.QuantityOrdered
,d.QuantityBackordered
,d.UDF_QTY_PULLED
,d.UDF_QTY_RECEIVED
,d.UDF_PROCESSED_IC
,h.UDF_PROCESS_INCOMPLETE
,h.UDF_ALLOCATED
,d.WarehouseCode
,h.UDF_DATE_ALLOCATED
FROM dbo.SO_SalesOrderDetail d
INNER JOIN dbo.SO_SalesOrderHeader h ON d.SalesOrderNo = h.SalesOrderNo
WHERE  h.OrderType IN('S','B') 
AND d.ItemType = '1'
AND d.WarehouseCode = '000'
ORDER BY d.SalesOrderNo

I'm assuming you created the original query using the graphical query designer in SSMS. I'll just say that the sooner you move away from that, the better. It has a number of really bad defaults that can lead to this kind of issue, amongst others.

Also, for future reference, if you want to submit sample data:

  1. Your images cut off the column headings, making the sample data meaningless

  2. Images are not usable for folks who are trying to reproduce the issues you're posting about

  3. The best option is to include DDL/CREATE TABLE statements for your tables, along with sample data formatted as INSERT statements, and provide expected results. The expected results can be in a tabular format, but please use text that can be copied and pasted.