Link to home
Start Free TrialLog in
Avatar of Peter Nordberg
Peter NordbergFlag for Sweden

asked on

Using OR in SQL clause

Hi,

I have a sql query looking like this:
SELECT o.orderID, o.invoiceID, o.customerID, c.firstname + ' ' + c.lastname AS Name, o.invoiceDate, o.PayDate, o.ocr, paymentStatusID, orderStatusID, ISNULL(printed,0) AS printed, currencyID, paymentTypeID  
FROM orders AS o INNER JOIN Customer AS c ON o.customerID = c.CustomerID  
WHERE (o.invoiceID > '0') AND (o.orgID = 11) AND orderSaved = 1 AND o.orderStatusID > 0 AND (old IS NULL OR Old = 0) 
AND orderStatusID = 5 
OR PaymentTypeID  = 12
ORDER BY CONVERT (BIGINT, o.invoiceID) DESC

Open in new window

The problem is now that when I use the OR keyword it chooses between this clause:
WHERE (o.invoiceID > '0') AND (o.orgID = 11) AND orderSaved = 1 AND o.orderStatusID > 0 AND (old IS NULL OR Old = 0) 
AND orderStatusID = 5 

Open in new window

and this:
OR PaymentTypeID  = 12

Open in new window


I can solve this by doing like this and this reflects the result I really want.
SELECT o.orderID, o.invoiceID, o.customerID, c.firstname + ' ' + c.lastname AS Name, o.invoiceDate, o.PayDate, o.ocr, paymentStatusID, orderStatusID, ISNULL(printed,0) AS printed, currencyID, paymentTypeID  
FROM orders AS o INNER JOIN Customer AS c ON o.customerID = c.CustomerID  
WHERE (o.invoiceID > '0') AND (o.orgID = 11) AND orderSaved = 1 AND o.orderStatusID > 0 AND (old IS NULL OR Old = 0) 
AND orderStatusID = 5 
OR PaymentTypeID  = 12
AND (o.invoiceID > '0') AND (o.orgID = 11) AND orderSaved = 1 AND o.orderStatusID > 0 AND (old IS NULL OR Old = 0) 
AND orderStatusID = 5 
ORDER BY CONVERT (BIGINT, o.invoiceID) DESC

Open in new window


I wonder however if there is a smarter way to use the OR keyword so I don't have to repeat everything in the WHERE clause every time I use it?!

Thanks for help!

Peter
SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Peter Nordberg

ASKER

Hi,

I tried like this:
SELECT o.orderID, o.invoiceID, o.customerID, c.firstname + ' ' + c.lastname AS Name, o.invoiceDate, o.PayDate, o.ocr, paymentStatusID, orderStatusID, ISNULL(printed,0) AS printed, currencyID, paymentTypeID  
FROM orders AS o INNER JOIN Customer AS c ON o.customerID = c.CustomerID  
WHERE (o.invoiceID > '0') AND (o.orgID = 11) AND orderSaved = 1 AND o.orderStatusID > 0 AND (old IS NULL OR Old = 0) 
AND (orderStatusID = 5  OR PaymentTypeID = 12)
ORDER BY CONVERT (BIGINT, o.invoiceID) DESC 

Open in new window


But then I get this result (two of the instances are doubled)

User generated image
Peter
They aren't duplicated. They has different orderID, orderStatusID and paymentTypeID.
If you want only one of them then you need to say which one should be returned.
You're right. I was to fast in my judgement.

Peter