Hide or show row in sql result depending on vaule

Might want to bracket that OR condition

@Gary - 'might want to' for readability or because of unexpected results? I'm always willing to defer to greater expertise, but have tested this on SQL fiddle and appears fine as is. I'd agree it could be bracketed for clarity, but I normally rely on T-SQL operator precedence AND>OR and dislike extraneous parentheses.

Here's the whole select statement for testing, with extra parentheses for clarity...

SELECT o.orderID, o.invoiceID, o.customerID, c.firstname + ' ' + c.lastname AS Name, o.invoiceDate, o.PayDate, o.ocr, o.orderDate, ISNULL(paymentStatusID, 0) AS paymentStatusID, o.paymentTypeID, orderStatusID, ISNULL(printed,0) AS printed, currencyID, paymentType, shipmentType, IsNull(sentOrder, 0) AS sentOrder  
FROM orders AS o INNER JOIN Customer AS c ON o.customerID = c.CustomerID  
INNER JOIN bklShipmentType AS bst ON bst.shipmentTypeID = o.shipmentTypeID 
INNER JOIN bklPaymentType AS bpt ON bpt.paymentTypeID = o.paymentTypeID 
WHERE (o.invoiceID > '0')
AND (o.orgID = 11)
AND orderSaved = 1
AND orderTypeID = 1
AND orderStatusID NOT IN (5, 8)
AND
( o.paymentTypeID  NOT IN(12,5,15) OR (o.paymentTypeID  IN(5,15) AND paymentStatusID=2)) 
ORDER BY orderDate DESC 

Select allOpen in new window

More for readability, not everyone knows the order and just makes more sense when you look back at it.
(...and I think it's better practise but thats a personal view)

Not for readability. It really needs the brackets there or else will filter only for paymentStatusID=2 no matter the paymentTypeID.

I don't wish to seem disagreeable, but on my 2008R2 system, this works as advertised. It does not filter out PaymentStatusID values <>2, apart from records with PaymentTypeID in (5,15)

I've  simplified the table structure and query to omit the joins in the example below.

CREATE TABLE [dbo].[Orders](
	[OrderID] [int] NULL,
	[InvoiceID] [int] NULL,
	[OrgID] [int] NULL,
	[OrderSaved] [int] NULL,
	[OrderTypeID] [int] NULL,
	[OrderStatusID] [int] NULL,
	[PaymentTypeID] [int] NULL,
	[PaymentStatusID] [int] NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Orders] 
VALUES (1, 1, 11, 1, 1, 1, 12, 2)
,(2, 2, 11, 1, 1, 1, 5, 2)
,(3, 3, 11, 1, 1, 1, 5, 3)
,(4, 4, 11, 1, 1, 1, 15, 2)
, (5, 5, 11, 1, 1, 1, 15, 3)
,(6, 6, 11, 1, 1, 1, 1, 14)
,(7, 7, 11, 1, 1, 1, 12, 3)
,(8, 8, 11, 1, 1, 1, 1, 7)
GO

SELECT o.orderID, o.invoiceID 
, ISNULL(paymentStatusID, 0) AS paymentStatusID, o.paymentTypeID, orderStatusID
FROM orders AS o  
 
WHERE (o.invoiceID > '0')
AND (o.orgID = 11)
AND orderSaved = 1
AND orderTypeID = 1
AND orderStatusID NOT IN (5, 8)
AND
o.paymentTypeID  NOT IN(12,5,15) OR o.paymentTypeID  IN(5,15) AND paymentStatusID=2

Select allOpen in new window

@Vitor, I'd really appreciate if you would paste my code into a query and run it to see if you get different results to me.

Simon, I did test it and worked as you said but then means that it's a SQL Server way of work. If you want to transfer the query to another RDBMS I don't think it will work as expected.
Then at least for readability you should use the brackets so the author knows that if for some reason he needs to move the paymentStatusID=2 he needs to move everything between the brackets.

Point taken about bracketing for readability and portability, Vitor. I appreciate you taking the time to test it. Thanks.

It was also good that you had the time to clarify it. I was so sure that the brackets would make all the difference in logic but you showed me that SQL Server uses the precedence of the operators. Thanks for that also. Living and learning :)

Should be the case in all DB's, certainly in MySQL it would work without the brackets.

Thanks both. In that case I stand by my earlier post as being the correct solution (with assistance from Vitor and Gary) :-)

No points for me please. My statement wasn't right.