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 = 12ORDER BY CONVERT (BIGINT, o.invoiceID) DESC
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 = 12AND (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
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
But then I get this result (two of the instances are doubled)
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.
I tried like this:
Open in new window
But then I get this result (two of the instances are doubled)
Peter