SELECT Query problem

[Craig Keightley]

I have the following table:
id | supRef | pmRef | status |
=============== ======
1 | 1 | 1 | 3 |
2 | 1 | 2 | 2 |
3 | 1 | 3 | 2 |
4 | 1 | 4 | 3 |
5 | 2 | 1 | 2 |
6 | 3 | 1 | 2 |
7 | 3 | 2 | 3 |
I need to create two queries, the first is simple:
SELECT * FROM table WHERE pmRef=1 AND status = 2

this is to show all suppliers with status of 2 (new) for a given purchasing
manager
The second table is a little more tricky (for me anyway)

When a purchasing manager updates a supplier, the status is set to 4 for
approved or 3 for declined.
After all purchasing managers have approved / declined the suppiler status
is changed accordingly
I need to be able to display a set of suppliers who have yet to be approved
/declined (status = 2) by other purchasing managers - and show the supplier
only once. It also needs to NOT SHOW the suppliers that are not associated
with a purchasing manager

eg:
id | supRef | pmRef | status |
=============== ======
2 | 1 | 2 | 2 |
3 | 1 | 3 | 2 |
any help would be very grateful

Craig

[Craig Keightley]

"Craig Keightley" <do**@spam.me > wrote in message
news:42******** *************@n ews-text.dial.pipex .com...

I have the following table:
id | supRef | pmRef | status |
=============== ======
1 | 1 | 1 | 3 |
2 | 1 | 2 | 2 |
3 | 1 | 3 | 2 |
4 | 1 | 4 | 3 |
5 | 2 | 1 | 2 |
6 | 3 | 1 | 2 |
7 | 3 | 2 | 3 |
I need to create two queries, the first is simple:
SELECT * FROM table WHERE pmRef=1 AND status = 2

this is to show all suppliers with status of 2 (new) for a given
purchasing manager
The second table is a little more tricky (for me anyway)

When a purchasing manager updates a supplier, the status is set to 4 for
approved or 3 for declined.
After all purchasing managers have approved / declined the suppiler status
is changed accordingly
I need to be able to display a set of suppliers who have yet to be
approved /declined (status = 2) by other purchasing managers - and show
the supplier only once. It also needs to NOT SHOW the suppliers that are
not associated with a purchasing manager

eg:
id | supRef | pmRef | status |
=============== ======
2 | 1 | 2 | 2 |
3 | 1 | 3 | 2 |
any help would be very grateful

Craig

So far, i have managed to create the following select statements from above
eg using pmRef of 1

id | supRef | pmRef | status |
=============== ======
1 | 1 | 1 | 3 |
2 | 1 | 2 | 2 |
3 | 1 | 3 | 2 |
4 | 1 | 4 | 3 |
5 | 2 | 1 | 2 |
6 | 3 | 1 | 2 |
7 | 3 | 2 | 3 |
8 | 4 | 1 | 3 |
9 | 4 | 2 | 2 |

SELECT * FROM table WHERE pmRef = 1 AND status = 2

id | supRef | pmRef | status |
=============== ======
2 | 1 | 2 | 2 |
9 | 4 | 2 | 2 |

Now for each of the above rows, I create a SELECT statement (generated using
PHP):

SELECT * FROM table WHERE supRef = 1 AND pmRef <> 2 AND status <> 2
SELECT * FROM table WHERE supRef = 4 AND pmRef <> 2 AND status <> 2

Combined Result:

id | supRef | pmRef | status |
=============== ======
1 | 1 | 1 | 2 |
4 | 1 | 4 | 3 |
8 | 4 | 1 | 3 |

Is there a more efficient way of doing this?
Any help please
Craig