CODEWITHSUNDEEP
c#sql
Dave
Dave

Reputation: 253

How to compare one value from a previous select and create a binary 1 or 0 field

How do I create a query that would allow me to combine these two queries the first quantity will be coming from a holding table which I need to compare with the second qauntity from a order line table.

Basically if the two match then I dont want to no about it but say if its 2 items done but line quantity is five it should show that their is 3 left to make.

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [DespatchedItemsId]
  ,[FirstLineQty]
  ,[Date]
  ,[SopLinkId]
  ,[DocumentNo]
  ,[ItemCode]
FROM [DespatchedItems]

So This query here should take into acount the first query so the matrix example would be 

MadeQaunitity     QtyOrder       LineReamin
3                 5              True and should show that there 2 left to 
                                 make
3                 3              False it should not be visible in the 
                                 results

This table holds the order line qty which should be used for the compairson

 select * from dbo.SOPOrderReturnLine where SOPOrderReturnLineID=5601381
 and LineQuantity != FirstLineQty

Please see the schema below which is linked via sopLinkId to soporderreturnlineid

enter image description here

Upvotes: 0

Views: 47

Answers (1)

ymz
ymz

Reputation: 6924

In that case I think that you should extend you join statement to include the criteria you originally placed in where clause.

SELECT     *
FROM       DespatchedItems items
INNER JOIN SOPOrderReturnLine orders ON 
           items.SopLinkId = orders.SOPOrderReturnLineID AND 
           items.Quantity != orders.LineQuantity

This query should return items that has a relation match and also have a different quantity amount

Upvotes: 1

Related Questions