Having difficulty to replicate entries in SQL query result
-
Good day all! Please assist if you can in resolving this my SQL query difficulty. I have 2 query results as follows; Table A 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 TABLE B 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
-
Good day all! Please assist if you can in resolving this my SQL query difficulty. I have 2 query results as follows; Table A 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 TABLE B 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
-
Take a look here: sql join - Google Search[^].
Thanks Richard for your response. Indeed, I have taken a look at the link suggestion. The 2 result sets I have above was achieved using 'Joins'. But achieving 'Table C' is the challenge as I currently can't figure a way out of it.
-
Thanks Richard for your response. Indeed, I have taken a look at the link suggestion. The 2 result sets I have above was achieved using 'Joins'. But achieving 'Table C' is the challenge as I currently can't figure a way out of it.
-
Thanks Richard for your response. Indeed, I have taken a look at the link suggestion. The 2 result sets I have above was achieved using 'Joins'. But achieving 'Table C' is the challenge as I currently can't figure a way out of it.
-
Given that we have no information about your source database tables it is not something that we can do for you.
Thanks, I have edited my original post to add an explanation about the entire process I intend to achieve. Table A and Table B is a result of SQL query combining multiple database tables. So I intend to retrieve Table C from the result of Table A and Table B
-
It would help is you would explain how to get Table C. It is not obvious to me just by looking at the data.
Everyone is born right handed. Only the strongest overcome it. Fight for left-handed rights and hand equality.
Thanks I have edited my original post to carry a better explanation on what I want.
-
Good day all! Please assist if you can in resolving this my SQL query difficulty. I have 2 query results as follows; Table A 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 TABLE B 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
What is not obvious is whether Table A has entries for "Gaurantor" and if there are a max of 2 records for each CID in Table A Assuming Table B has a single entry for each CID Select * from TableB inner join TableB B and TableA A on A.CID = B.CID
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
-
What is not obvious is whether Table A has entries for "Gaurantor" and if there are a max of 2 records for each CID in Table A Assuming Table B has a single entry for each CID Select * from TableB inner join TableB B and TableA A on A.CID = B.CID
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
CID in Table A is always single entry as a customer can't have two running loans as same time and in Table B CID is still single entry as is the account balance of all customers. But Table A.CID is always a subset of Table B.CID. "guarantor" entries under CREDENTIAL column in Table C is just a string input ( like select 'guarantor' as CREDENTIAL) or (isnull(CREDENTIAL, 'guarantor')) to make me identify that entry as details of a particular guarantor while UNIQUE_ID entries in Table C helps groups individual loan together as both the loan collector and guarantors will have same entries. CID in Table C can have multiple same values but all entries must be drawn from the primary column Table B.CID
-
CID in Table A is always single entry as a customer can't have two running loans as same time and in Table B CID is still single entry as is the account balance of all customers. But Table A.CID is always a subset of Table B.CID. "guarantor" entries under CREDENTIAL column in Table C is just a string input ( like select 'guarantor' as CREDENTIAL) or (isnull(CREDENTIAL, 'guarantor')) to make me identify that entry as details of a particular guarantor while UNIQUE_ID entries in Table C helps groups individual loan together as both the loan collector and guarantors will have same entries. CID in Table C can have multiple same values but all entries must be drawn from the primary column Table B.CID
Ok that is very straight forward, pick your primary table A or B as they have a single value for the CID. Use inner joins from A to B and A to C and select the fields you need to display. If there are missing records in your secondary tables then use left outer joins.
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
-
Ok that is very straight forward, pick your primary table A or B as they have a single value for the CID. Use inner joins from A to B and A to C and select the fields you need to display. If there are missing records in your secondary tables then use left outer joins.
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
I tried all I could, but still failed to see anyway to directly generate Table C from Table A and B. I have to generate a new Table D (which looks like Table C - Table A) from my based database tables. Using Table A Union Table D now gives me Table C. Thanks a lot for your support.
-
I tried all I could, but still failed to see anyway to directly generate Table C from Table A and B. I have to generate a new Table D (which looks like Table C - Table A) from my based database tables. Using Table A Union Table D now gives me Table C. Thanks a lot for your support.
My bad, I thought table C existed with CID and Credential. Where do you expect to get the credential for
Guarantorfrom it is not in table A or B in your examples.Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
-
My bad, I thought table C existed with CID and Credential. Where do you expect to get the credential for
Guarantorfrom it is not in table A or B in your examples.Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
"guarantor" entries under CREDENTIAL column in Table C is just a string input ( like select 'guarantor' as CREDENTIAL) or (isnull(CREDENTIAL, 'guarantor')) to make me identify that entry as details of a particular guarantor. Since I have gotten a work around for this particular challenge, how do I mark my question as 'Closed' or 'Answered'?
-
"guarantor" entries under CREDENTIAL column in Table C is just a string input ( like select 'guarantor' as CREDENTIAL) or (isnull(CREDENTIAL, 'guarantor')) to make me identify that entry as details of a particular guarantor. Since I have gotten a work around for this particular challenge, how do I mark my question as 'Closed' or 'Answered'?
paul4everyone wrote:
how do I mark my question as 'Closed' or 'Answered'?
You don't. Generally if you work it out it is nice to post the solution.
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP