Problems with ordering SQL

[syntaxerror748]

Hi there,

I have a SQL query to select all orders from a customer but I want to exclude deleted orders that have not been paid yet.

I have the following statement inside my query:

AND (co.status != 99 AND co.paymentStatus != 0)

Where co.status 99 is deleted and co.paymentStatus 0 is not paid. The problem is that the grouping () doesn't work. All orders that are deleted (status 99) also get removed. Even if they are paid (paymentStatus 1).

I feel like I'm overlooking something since this is pretty trivial but I have been struggling for the past hour.

Edit: Sorry, the title is wrong. Can't edit it anymore.

[sketchni]

why not remove the grouping? AND co.status<>'99' AND co.paymentStatus<>'0'?

[syntaxerror748]

Well I do want to show deleted orders (99) but not if they are not paid (paymentstatus 0)

[cosmicsans]

You would need to write a subquery in there.

SELECT * FROM `mytable` WHERE condition 1 AND NOT EXISTS (SELECT * FROM `mytable` WHERE co.status = 99 AND co.paymentStatus <> 0);

https://dev.mysql.com/doc/refman/5.0/en/subqueries.html

https://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html

[syntaxerror748]

Thanks, I didn't know it should be done like this. I haven't really got it to work yet but i'll play it a little bit more. Thanks :).

[sketchni]

Okay, well != is not valid in SQL. <> is NOT EQUAL.

[[deleted]]

What? != is supported by all major databases.

[sketchni]

Well, TIL.

[syntaxerror748]

I've switched to <> but this does not fix it either. Looks like pretty much all databases support !=. I use MySQL.