Avatar of Peter Nordberg
Peter Nordberg
Flag 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
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Peter Nordberg

8/22/2022 - Mon
SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Phillip Burton

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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)

SQL result
Peter
Vitor Montalvão

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.
Peter Nordberg

ASKER
You're right. I was to fast in my judgement.

Peter
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes