PHP SQL - conditional SELECT query

Author

conditional SELECT query

sylvian stone

2005-10-19, 7:56 am

Hi,

I'm trying to get a return value from a select statement, but this is
proving more difficult than I thought.

I have three tables:

order
client
security

each of which has a shared row called 'client_id'

What I need to do is do a look-up on the SECURITY table to check for
all client_id 's on both the security and order tables based on a
specified username - BUT only if two additional entries on the order
table, 'status' and 'order_id' are set to NULL.

I would execute:

"SELECT client_id FROM security LEFT JOIN order ON security.client_id =
order.client_id WHERE security.username = 'myusername' LIMIT 1"

For reasons that are too boring to go into, people are free to leave
multiple entries, but I want to sweep through the database and erase
all client entries linked to a specified username entries EXCEPT those
clients which have completed a transaction on the order table - which
would be confirmed if order.status and order.order_id are set to
something other than NULL.

Therefore, the above query would pull all the necessary customers, but
not distinguish between those with at least one completed order, and
those whose details have been logged but never actually completed an
oder from start to finish.

Once I have the necessary client_ids, I can run delete statements on
the tables shown above.

Is it possible to include this is one SQL statement, or do I need to
run this through two SQL statements to get the necessary data ?

Thanks in advance for any suggestions.

SS

Hilarion

2005-10-19, 7:56 am

> I'm trying to get a return value from a select statement, but this is
> proving more difficult than I thought.
>
> I have three tables:
>
> order
> client
> security
>
> each of which has a shared row called 'client_id'
>
> What I need to do is do a look-up on the SECURITY table to check for
> all client_id 's on both the security and order tables based on a
> specified username - BUT only if two additional entries on the order
> table, 'status' and 'order_id' are set to NULL.
>
> I would execute:
>
> "SELECT client_id FROM security LEFT JOIN order ON security.client_id =
> order.client_id WHERE security.username = 'myusername' LIMIT 1"
>
> For reasons that are too boring to go into, people are free to leave
> multiple entries, but I want to sweep through the database and erase
> all client entries linked to a specified username entries EXCEPT those
> clients which have completed a transaction on the order table - which
> would be confirmed if order.status and order.order_id are set to
> something other than NULL.
>
>
> Therefore, the above query would pull all the necessary customers, but
> not distinguish between those with at least one completed order, and
> those whose details have been logged but never actually completed an
> oder from start to finish.
>
> Once I have the necessary client_ids, I can run delete statements on
> the tables shown above.
>
>
> Is it possible to include this is one SQL statement, or do I need to
> run this through two SQL statements to get the necessary data ?

Something like this should list the records:

SELECT DISTINCT
`security`.client_id
FROM
`security` LEFT OUTER JOIN
`order` ON `security`.client_id = `order`.client_id
WHERE
`security`.username = 'myusername' AND
`order`.`status` IS NULL AND
`order`.order_id IS NULL

Hilarion

PS.: This will also list client IDs which do not have any entry in "order" table.
PPS.: You should not use "order" word as a table name because it's a reserved
word. If you have to use it then quote it using "`" sign. In MySQL 5.0
"security" and "status" are also a reserved keyword. List of reserved
keywors can be found here:
(v4.1) http://dev.mysql.com/doc/refman/4.1...rved-words.html
(v5.0) http://dev.mysql.com/doc/refman/5.0...rved-words.html
(v5.1) http://dev.mysql.com/doc/refman/5.1...rved-words.html

Sponsored Links