|
|
Posted by sylvian stone on 10/19/05 13:45
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
Navigation:
[Reply to this message]
|