There are no records where B2.direction='OUT' and B2.SCN IS NULL.
When B2.SCN IS NULL, B2.direction IS NULL, not 'OUT'
WHERE A1.Direction='IN' and A1.IID != B2.IID and B2.Direction IS NULL
Menard, Paul wrote:
>I am converting a system from ASP (Active Server Pages) running access97
>database to PHP using MySQL. So far everything runs terrific. Until now that
>is. Don't worry this is NOT a PHP or ASP question.
>In the ASP I have a sub-query that runs against a table. I have spent the
>last weekend trying to convert this to a LEFT JOIN statement.
>Here are some details. The table contains transactions. There is a record
>(row) created for the inbound transactions and a record (row) created for
>the outbound transactions. Both of these records do have a match, SCN. And
>each has a direction indicator 'IN' or 'OUT'. The purpose of the ASP/PHP Web
>page is to report those inbound transactions that do NOT have an outbound
>Table structure is something like
>Direction char(10) (Can be 'IN' or 'OUT' values only)
>So, with this simple above table I've tried to determine those rows without
>the pairs as follows:
>SELECT A1.* from Trans AS A1
>LEFT JOIN Trans AS B2
>AND B2.SCN IS NULL
>When I run this I get a message from MySQL (or phpMyAdmin) that the query
>ran successful, but I have no output. The current table contains a sample of
>the larger database. I have hand created rows in the table to make sure the
>data is correct. Now it's just a matter of figuring out the query.
>Also, the design of the table needs to be a separate row per transactions.
>This means please do not respond that I could combine the rows and have a
>single row per inbound and outbound transactions. This will not work because
>in the second phase of this project there will be multiple outbound
>transactions. I will still need to determine those inbound transactions that
>do not have at least one outbound matching pair.
>What am I missing here? Any help?
>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 <mysql-thread116486@stripped>
>To unsubscribe, e-mail
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php