|
|
Posted by Hilarion on 10/19/05 14:14
> 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/en/reserved-words.html
(v5.0) http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html
(v5.1) http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html
[Back to original message]
|