List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:August 6 2002 3:07pm
Subject:Re: sub-select work around help needed- retry
View as plain text  
There are no records where B2.direction='OUT' and B2.SCN IS NULL.
When B2.SCN IS NULL, B2.direction IS NULL, not 'OUT'

Try:
WHERE A1.Direction='IN' and A1.IID != B2.IID and B2.Direction IS NULL

Menard, Paul wrote:

>Hello all,
>
>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
>transactions pair. 
>
>Table structure is something like 
>
>IID		auto-increment
>Customer   	char(100)
>DateTime   	datetime
>SCN        	char(20)
>Direction  	char(10)  (Can be 'IN' or 'OUT' values only)
>Filename   	char(100);
>
>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
>ON A1.SCN=B2.SCN
>WHERE A1.Direction='IN'
>AND B2.Direction='OUT'
>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?
>
>Paul
>
>
>---------------------------------------------------------------------
>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
> <mysql-unsubscribe-gerald_clark=suppliersystems.com@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>


Thread
sub-select work around help needed- retryPaul Menard6 Aug
  • Re: sub-select work around help needed- retryGerald Clark6 Aug
  • Re: sub-select work around help needed- retryRalf Narozny6 Aug
RE: sub-select work around help needed- retryDuncan Salada6 Aug
RE: sub-select work around help needed- retryPaul Menard6 Aug