hijckBoy hijckBoy - 4 months ago 7
MySQL Question

mysql - selct order_id from tbl where orders_status_id has never x

I am using old mysql version with php version 4 on my website.

My table structure :

| orders_status_history_id | orders_id | orders_status_id |
| 1 | 22 | 3 |
| 2 | 23 | 2 |
| 3 | 24 | 5 |
| 4 | 23 | 8 |
| 5 | 25 | 5 |
| 6 | 30 | 1 |
| 7 | 26 | 2 |
| 8 | 22 | 4 |
| 10 | 22 | 5 |
| 15 | 48 | 6 |
| 16 | 52 | 8 |
| 17 | 32 | 9 |
| 18 | 50 | 5 |
| 19 | 24 | 5 |
| 22 | 20 | 8 |
| 23 | 1001 | 1 |
| .. | .. | .. |
| .. | .. | .. |


My table has 200000+ rows and
orders_status_history_id
is
auto increment


So my question is this :

How do i get
orders_status_history_id
where
orders_status_id
has never had id 5 of
orders_id
?

For examaple :
if
orders_id
22 had ever
orders_status_id 5
then skip
orders_id
22 and
orders_id
23 had never
orders_status_id
5 then show me this
orders_id
23

So i expect like this :

| orders_status_history_id | orders_id |
| 3 | 24 |
| 4 | 23 |
| 17 | 32 |


Because these
orders_id
has never had
orders_status_id
id equal to 5.
orders_status_history_id = 3
has never had
orders_id
= 5


I hope you understand. Its little bit difficult to explain you for me.

I have try to do something like this

$sql = "SELECT orders_status_history_id, orders_id, orders_status_id FROM orders_status_history_tbl where orders_status_id not 5";


but it returns all array of where
orders_status_id
is not equal to 5.

Ty for helping

For any further information please ask me.

Answer

UPDATED

SELECT 
     orders_status_history_id,order_id
FROM 
    orders_status_history_tbl 
Where orders_id NOT IN 
  (
      Select Distinct
          orders_id 
      From 
          orders_status_history_tbl 
      Where 
          orders_status_id = 5
  )