Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Select query in joint tables

2 views
Skip to first unread message

Gordon

unread,
Nov 15, 2007, 12:29:42 PM11/15/07
to
I need some help.

I have two tables in the following format:

ARSIA table

IDCUST IDTYPE DateStart ...
A87624 Parking 5/1/2006
A87624 Rent 11/1/2004
A50066 Rent 2/1/2005


ARSIAO table

IDCUST IDTYPE OPTFIELD VALUE
A50066 Rent BankNo 002
A50066 Rent TransitNo 20511
A50066 Rent AccountNo 493-29399-2
A87624 Parking BankNo
A87624 Parking TransitNo
A87624 Parking AccountNo
A87624 Rent BankNo 003
A87624 Rent TransitNo 10850
A87624 Rent AccountNo 63-1055709

I would like to list all customers with banking information, ie
[Value] field is non blank.
This should be the result:
IDCUST IDTYPE BankNo TransitNo AccountNo
A50066 Rent 002 20511 493-29399-2
A87624 Rent 003 10850 63-1055709


I tried the following query and it doesn't work well.
SELECT TOP 100 PERCENT a.IDCUST, a.IDTYPE, b.[VALUE] AS BankNo, c.
[VALUE] AS TransitNo, d.[VALUE] AS AccountNo
FROM ARSIA a INNER JOIN
ARSIAO b ON a.IDCUST = b.IDCUST INNER JOIN
ARSIAO c ON a.IDCUST = c.IDCUST INNER JOIN
ARSIAO d ON a.IDCUST = d.IDCUST
WHERE (b.OPTFIELD = 'Bankno') AND (c.OPTFIELD = 'transitno') AND
(d.OPTFIELD = 'accountno') AND (RTRIM(b.[VALUE]) <> '') AND (RTRIM(c.
[VALUE]) <> '')
AND (RTRIM(d.[VALUE]) <> '')

It gives me the following result:
IDCUST IDTYPE BankNo TransitNo AccountNo
A50066 Rent 002 20511 493-29399-2
A87624 Parking 003 10850 63-1055709
A87624 Rent 003 10850 63-1055709


How to correct this?

Thanks.

Gordon

unread,
Nov 15, 2007, 2:37:45 PM11/15/07
to
The problem is solved.

Thanks.

0 new messages