CODEWITHSUNDEEP
sqlsql-server
PicoDeGallo
PicoDeGallo

Reputation: 608

Remove row from SELECT based on column value

I have a complex stored procedure that I need to be able to hide a specific row from a table joined onto the primary table. The criteria of hiding this row is based on whether a value (DeductMoney) in the primary table (Orders) is a 1. My stored procedure is similar to this:

SELECT 
  O.FileNumber
  ,ML.Line
  ,ML.Price
  ,ML.Description
FROM Orders O
INNER JOIN Main M 
  ON O.OrdersID = M.OrdersID 
INNER JOIN MainLine ML
  ON ML.MainID = M.MainID
WHERE 
  M.MainID = @MainID

The result set currently looks like the following:

FileNumber | Line | Price | Description
---------------------------------------
0001       | 501  | $200  | DescriptionA
0001       | 502  | $300  | DescriptionB
0001       | 503  | $150  | DescriptionC
0001       | 504  | $100  | DescriptionD
0001       | 505  | $75   | DescriptionE
0001       | 506  | $500  | DescriptionF

Now from the Orders table, there is a column named DeductMoney. This column ONLY applies to one specific row of the returned result set, Line 506. The DeductMoney column can either be a 0 or 1, and if it is a 1, I need to completely remove the row from the SQL SELECT. So essentially, if DeductMoney = 1, remove the row that Line 506 exists.

This data is unfortunately not being passed to SSRS, so I cannot filter it at that level. Does anyone have thoughts of how I would carry this out? Is it possible to do it via a CASE statement? Any help is much appreciated!

Remove row from SELECT based on column value

Upvotes: 0

Views: 646

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You need to add logic to your where clause:

WHERE M.MainID = @MainID AND
      NOT (Line = 506 and DeductMoney = 1)

Upvotes: 2

BlackICE
BlackICE

Reputation: 8916

Wouldn't this work, or am I misreading something?

SELECT 
  O.FileNumber
  ,ML.Line
  ,ML.Price
  ,ML.Description
FROM Orders O
INNER JOIN Main M 
  ON O.OrdersID = M.OrdersID 
INNER JOIN MainLine ML
  ON ML.MainID = M.MainID
WHERE 
  M.MainID = @MainID
  AND DeductMoney <> 1

Upvotes: 0

Related Questions