Actually, you should be able to get the data in one query: in general,
any time you are doing a restriction on some value being in a set of
values (such as order_id not in <some list built from a subselect>) you
can rewrite the query using LEFT OUTER JOIN's. In this case, the
solution would be something like:
SELECT *
FROM orders o
INNER JOIN transaction_log t
ON o.order_id = t.order_id
AND t.event_log_id = 1
LEFT OUTER JOIN transaction_log t2
ON t.order_id = t2.order_id
AND t2.event_log_id = 3
WHERE t2.order_id IS NULL;
The way this works is to join across the transaction_log table twice:
first, to retrieve all rows that reference the given order and have an
event log id of 1, and second to retrieve all rows that reference the
given order id and have an event log id of 2. We then restrict to get
only those rows that do not have valid values in the second join's
results (namely, those transactions that have an event id of 1, from the
initial (inner) join, but do not have a corresponding event id of 3,
from the second (outer) join).
On Sun, 2002-08-25 at 09:43, Brian Moon wrote:
> Ok, I did not make the correlation between what rows you wanted and what the
> data was. You are not getting this in one query. It would require a
> sub-select. However, simple, but possibly slow queries will get it.
>
> select * from transaction_log where event_log_id=3
>
> get those back and
>
> select order_id from transaction_log where order_id not in ({id list})
>
> The list could be pretty big I guess.
>
> Brian.
>
> ----- Original Message -----
> From: "Paul Maine" <[EMAIL PROTECTED]>
> To: "Brian Moon" <[EMAIL PROTECTED]>; "MySQL MySQL" <[EMAIL PROTECTED]>
> Sent: Sunday, August 25, 2002 7:02 AM
> Subject: RE: SQL JOIN Challenge - Help Please
>
>
> | This is deceptively simple, and I very much appreciate your suggestion -
> but
> | it doesn't work. Your suggestion still returns records that I should not
> | see. Once again, there is a one to many relationship between an order_id
> in
> | the order_id in the transaction response table. Please see my original
> | email.
> |
> | Thank you
> | Paul
> | sql query
> | -----Original Message-----
> | From: Brian Moon [mailto:[EMAIL PROTECTED]]
> | Sent: Sunday, August 25, 2002 12:11 AM
> | To: Paul Maine; MySQL MySQL
> | Subject: Re: SQL JOIN Challenge - Help Please
> |
> |
> | If I read right, this is a pretty basic join so I am assuming that you
> have
> | never done a join in your life.
> |
> | select
> | distinct order.order_id
> | from
> | order
> | inner join
> | transaction_log on
> | order.order_id=transaction_log.order_id and
> | transaction_log.event_log_id <> 3
> |
> | Brian.
> |
> |
> | ----- Original Message -----
> | From: "Paul Maine" <[EMAIL PROTECTED]>
> | To: "MySQL MySQL" <[EMAIL PROTECTED]>
> | Sent: Saturday, August 24, 2002 7:31 PM
> | Subject: SQL JOIN Challenge - Help Please
> |
> |
> | | I have an order table and a transaction log table. The order table has
> an
> | | order_id and a number of other columns that are not germain to my
> | question.
> | | The transaction log has an order_ID and an event_log_id column. The
> order
> | id
> | | from the order table will have a one to many relationship to the
> order_id
> | | data in the transaction log table. The event_log_id is an integer that
> | | assumes a value of 1 if the credit card is authorized, a value of 2 if
> the
> | | credit card is denied or some other type of error, and a value of 3 if
> the
> | | credit card is delay captured.
> | |
> | | I need to create a query that would return all of the order.order_ids
> that
> | | are authorized but have not been delayed captured. How can I accomplish
> | this
> | | task?
> | |
> | | Thank You
> | | Paul
> | | sql query
> | |
> | |
> | | ---------------------------------------------------------------------
> | | Before posting, please check:
> | | http://www.mysql.com/manual.php (the manual)
> | | http://lists.mysql.com/ (the list archive)
> | |
> | | To request this thread, e-mail <[EMAIL PROTECTED]>
> | | To unsubscribe, e-mail
> | <[EMAIL PROTECTED]>
> | | Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> | |
> | |
> |
> |
> | ---------------------------------------------------------------------
> | Before posting, please check:
> | http://www.mysql.com/manual.php (the manual)
> | http://lists.mysql.com/ (the list archive)
> |
> | To request this thread, e-mail <[EMAIL PROTECTED]>
> | To unsubscribe, e-mail
> | <[EMAIL PROTECTED]>
> | Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> |
> |
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
