Joining A Table Onto Itself

[Guest]

H

I'm trying to join a table onto itself so that I can duplicate a specific (but common field) reference to eacg line detail

Sampe Data is as follows

Period Trx Date Reference Amount Description Alloc Re
2003050 7/31/2003 WWH0034696 55.00 ABC Company Inc 12352
2003050 7/31/2003 WWH0034698 59.00 Invoice-General 12352
2003050 8/01/2003 1053 155.00 PAYMENT 12352

Note that the Alloc Ref column data is always the same

The SQL that I'm having trouble with is as follows

SELECT m1.*,m2.Referenc
FROM m1, m
WHERE m1.Alloc Ref = m2.Alloc Re
AND m1.Description="PAYMENT
AND m2.Description="PAYMENT

What should happen is that the data replicates itself next to the columns that are already there. In addition to that, it should extract out the data under Reference if the Description field has "PAYMENT" in it.

Any suggestions or a revised example would be really appreciated as I'm banging my head on it at this point. I suspect part of the issue is that the punctuation may not be correct in some places. Alternatively, if there is an easier way that would be great too

Thank
Davi

 

[Gerald Stanley]

Are m1 and m2 supposed to be table aliases. If so the
actual table name needs to be included as well. Are both
deascription columns meant to have 'PAYMENT' in which case
you are likely to end up just joining the row onto itself.
I would also advocate an INNER JOIN rather than a WHERE
clause for the table relationship. Try something along the
lines of
SELECT m1.*,m2.Reference
FROM YourTable m1 INNER JOIN YourTable m2 ON m1.Alloc Ref =
m2.Alloc Ref
WHERE m1.Description="PAYMENT"
AND m2.Description<>"PAYMENT"

You will have to change YourTable to suit your app and may
have to change the WHERE clauses depending upon which table
you want to see all the columns.

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
Hi

I'm trying to join a table onto itself so that I can

duplicate a specific (but common field) reference to eacg
line detail.

Sampe Data is as follows:

Period Trx Date Reference

Amount Description Alloc Ref

2003050 7/31/2003 WWH0034696 55.00 ABC Company Inc 123525
2003050 7/31/2003 WWH0034698 59.00

Invoice-General 123525

2003050 8/01/2003 1053

155.00 PAYMENT 123525

Note that the Alloc Ref column data is always the same.

The SQL that I'm having trouble with is as follows:

SELECT m1.*,m2.Reference
FROM m1, m2
WHERE m1.Alloc Ref = m2.Alloc Ref
AND m1.Description="PAYMENT"
AND m2.Description="PAYMENT"

What should happen is that the data replicates itself next

to the columns that are already there. In addition to that,
it should extract out the data under Reference if the
Description field has "PAYMENT" in it.

Any suggestions or a revised example would be really

appreciated as I'm banging my head on it at this point. I
suspect part of the issue is that the punctuation may not
be correct in some places. Alternatively, if there is an
easier way that would be great too.

 

[Gerald Stanley]

Dave

I think that my suggestion will work providing the WHERE
clause is adjusted to
WHERE m1.Description<>"PAYMENT"
AND m2.Description="PAYMENT"

That should bring up all the details for a row on the table
providing that there is another row with the same AllocRef
and a Description of "Payment"

Hope This Helps
Gerald Stanley

-----Original Message-----
Hi Gerald

Thanks for the advice -much appreciated. You're right as

well, it simply creates a loop and actually overwrites all
the data in the source fields as well as creating the new
chq field. So I have abit of work to do on I think.

I was hoping only to extract the data out of the invoice

field if the description field has the word "payment"
recorded in it (ie based on the allocation number). If the
allocation number does not match to a field that has the
word "payment", then it would simply leave the field blank.