How to go about the following requirement in SQL???

#DBMS Query

Say I have a table with fields empno,loan_ref_id,amount
Say an employee has obtained 2 loans and his recovey for a particular month is as follows:

empno      loan_ref_id         amount
1                 123                    100
1                 456                    200

here empno can be repetitive as he can avail more than one loan. I tried to make it as follows to reduce the number of rows in the resultant set

empno            loan_ref_id1             amount              loan_ref_id2          amount

1                      123                          100                    456                        200

I tried to execute the following query but output is not as expected.

Query:
select a1.empno,a1.refno,a1.amt,a2.refno,a2.amt from table a, table b where a.empno=b.empno and a.ref1 != b.ref1.

What is wrong with the above query any clue???

Ps: Instead of 1 row many rows have been thrown as follows

empno       loan_ref_id1             amount             loan_ref_id2            amount

1                    123                       100                    123                        100
1                    123                       100                    123                        100
1                     123                      100                   123                         100 and so on..

see in the above result amount 200 missing and loan_ref_id2 (456) is missing...

7Comments
Sumit Verma sumitverma 11 Jan 2017 02:05 pm

Write your query clearly, use loan_ref_id1  and   loan_ref_id2 .

Ranita Biswas ranita 11 Jan 2017 02:43 pm

Being consistent with the naming you have used in the first table and assuming that your table name is emploan, the query should be:

select a.empno, a.loan_ref_id, a.amount, b.loan_ref_id, b.amount from emploan a, emploan b where a.empno=b.empno and a.loan_ref_id <> b.loan_ref_id;

Sumit Verma sumitverma 11 Jan 2017 03:15 pm

@ranita ma'am, Is duplicate column name like 'amount' allowed in a table ?

Ranita Biswas ranita 11 Jan 2017 03:25 pm

Yes, I forgot to mention that. Duplicate column names as he has shown in second table is not allowed. However, if the query written by me is ran, it will produce unique column names because a or b will be prefixed with the names. If new names for columns need to be given, we need to use 'as' to assign those.

Suman suman08513 11 Jan 2017 03:41 pm

@ranita ma'am, I have executed the query but lot of duplicates come in the result set.

Eg: For the employee with empno=24601111449, two recoveries have been affected with refno 052730 and 198065 respectively. And the amounts for the repsective refno are 1400 and 1500. The earningdeduction code field says about the type of loan.

 

sent the snaps to ur fb as I am unable to attach them here. Please do the neeful

Please refer the snaps. In the first snap the employee has 2 recoveries for the month of Dec,2016. Hence 2 rows have been fetched. 

Want to make it one row as shown below:

empno                 refno1      amt    earningdeduction       refno2       amt         earningdeduction

24601111449     052730     1400    RY909C                    198065     1500       RY941C

 

Please find the second screenshot for the employee 2461111449 fetched lot of duplicates instead of one row..

Ranita Biswas ranita 11 Jan 2017 05:32 pm

The extra conditions as you are putting in the query on billunit, payperiod, and earningdeduction should be done for both tables a and b.

Suman suman08513 11 Jan 2017 06:51 pm

@ranita ma'am after putting the extra conditions to both tables a and b the result of the query equal to the my original query i.e returing 2 records as below.

empno            loan_ref_id1             amount              loan_ref_id2          amount

1                      123                          100                    123                       100

1                       345                         200                    345                       200

 

screenshot has been sent to ur fb pls verify...