|Good day all!
Please assist if you can in resolving this my SQL query difficulty.
I have 2 query results as follows;
CID | NAME | CREDENTIAL | BAL | GID1 | GID2 | UNIQUE_ID
01 | AAA | debtor | 20 | 03 | 02 | 01-03,02
02 | BBB | debtor | 15 | 01 | 05 | 02-01,05
03 | CCC | debtor | 10 | 02 | 04 | 03-02,04
CID | NAME | BAL
01 | AAA | 20
02 | BBB | 15
03 | CCC | 10
04 | DDD | 5
05 | EEE | 2
06 | FFF | 4
Now I need help in getting TABLE C that should look like this;
CID | NAME | CREDENTIAL | BAL | UNIQUE_ID
01 | AAA | debtor | 20 | 01-03,02
03 | CCC | guarantor | 10 | 01-03,02
02 | BBB | guarantor | 15 | 01-03,02
02 | BBB | debtor | 15 | 02-01,05
01 | AAA | guarantor | 20 | 02-01,05
05 | EEE | guarantor | 2 | 02-01,05
03 | CCC | debtor | 10 | 03-02,04
02 | BBB | guarantor | 15 | 03-02,04
04 | DDD | guarantor | 5 | 03-02,04
WHAT I INTEND TO ACHIEVE
This is like a loan collection summary.
CID = General Primary Key for every customer
NAME = Customer names
CREDENTIAL = To help me diffentiate between the loan collector and the guarantors, so that I can group in my front end VB application.
BAL = everyone account balance
GID = guarantor for the loan collector, GID is a subset of CID
UNIQUE_ID = A string to combine the CID and the GID for a loan transaction so that I can easily isolate everything about a single loan transaction.
Table A is an abridged form of each loan collection transaction.
Table B carries the account balance for every customer.
Now, I expect Table C to carry all loan record in Table A and use the entries in GID1 and GID2 columns to fetch their corresponding details from Table B while retaining the Unique_ID entries from Table A so that sorting by Unique_ID will bring all loans together.
Please assist me with a way to achieve this, I have been thinking for days, yet no breakthrough.
Thanks in advance
modified 28-Jan-19 21:07pm.