sql query
-
I have a table with the following data Row | IssuedBankId | paidBankId | Payable | Receivable 1 | 1 | 2 | 1000 | 2000 _____________________________________________________________ 2 | 1 | 3 | 1100 | 3000 _____________________________________________________________ 3 | 1 | 4 | 4000 | 5000 _____________________________________________________________ 4 | 2 | 1 | 2000 | 1000 _____________________________________________________________ 5 | 3 | 1 | 3000 | 1100 _____________________________________________________________ 6 | 4 | 2 | 5000 | Null _____________________________________________________________ 7 | 5 | 4 | 1000 | Null I want to eliminate rows 4,5 bcoz we have the same info in row 1,2 respectively. Just payable, receivable amnts are interchanged. how can I get it. Please give me some Idea. Thaking You, Kiran.
-
I have a table with the following data Row | IssuedBankId | paidBankId | Payable | Receivable 1 | 1 | 2 | 1000 | 2000 _____________________________________________________________ 2 | 1 | 3 | 1100 | 3000 _____________________________________________________________ 3 | 1 | 4 | 4000 | 5000 _____________________________________________________________ 4 | 2 | 1 | 2000 | 1000 _____________________________________________________________ 5 | 3 | 1 | 3000 | 1100 _____________________________________________________________ 6 | 4 | 2 | 5000 | Null _____________________________________________________________ 7 | 5 | 4 | 1000 | Null I want to eliminate rows 4,5 bcoz we have the same info in row 1,2 respectively. Just payable, receivable amnts are interchanged. how can I get it. Please give me some Idea. Thaking You, Kiran.
I don't understand - is there a constraint preventing you from deleteing the data ? if not, I'd make a backup of the table, then delete from <table> where row in (4,5); you don't say which database/sql you're using, so Im assuming your sql allows the 'in (4,5)' - not sure which standard it is ... 'g'
-
I don't understand - is there a constraint preventing you from deleteing the data ? if not, I'd make a backup of the table, then delete from <table> where row in (4,5); you don't say which database/sql you're using, so Im assuming your sql allows the 'in (4,5)' - not sure which standard it is ... 'g'
Thank U 'g', I am using sql server database. The data which I have given is from view not from table (sorry for mentioning it as table). What I exactly need is , I want to exclude redundant data while displaying, here when Bank '1' is payable "xxx" amnt to Bank '2', and receivable 'yyy' from bank '2', then bank '2' is receivable 'xxx' from bank '1', and payable 'yyy' to bank '1'. Hence my report should consists of either bank '1' info, or bank '2' info. In the same manner all banks info in one query.And field 'Row' is not a column. That I hav given for referance purpose. Kiran
-
I have a table with the following data Row | IssuedBankId | paidBankId | Payable | Receivable 1 | 1 | 2 | 1000 | 2000 _____________________________________________________________ 2 | 1 | 3 | 1100 | 3000 _____________________________________________________________ 3 | 1 | 4 | 4000 | 5000 _____________________________________________________________ 4 | 2 | 1 | 2000 | 1000 _____________________________________________________________ 5 | 3 | 1 | 3000 | 1100 _____________________________________________________________ 6 | 4 | 2 | 5000 | Null _____________________________________________________________ 7 | 5 | 4 | 1000 | Null I want to eliminate rows 4,5 bcoz we have the same info in row 1,2 respectively. Just payable, receivable amnts are interchanged. how can I get it. Please give me some Idea. Thaking You, Kiran.
If I inderstood you corectly, you want to eliminate rows 4 and 5 because the transfer is in opposite order base on bank id's and amounts. If that's correct you could write something like:
select *
from tablename t1
where not exists (select 1
from tablename t2
where t2.paidbankid = t1.issuedbankid
and t2.payable = t1.receivable
and t2.receivable = t1.payable)The need to optimize rises from a bad design.My articles[^]
-
If I inderstood you corectly, you want to eliminate rows 4 and 5 because the transfer is in opposite order base on bank id's and amounts. If that's correct you could write something like:
select *
from tablename t1
where not exists (select 1
from tablename t2
where t2.paidbankid = t1.issuedbankid
and t2.payable = t1.receivable
and t2.receivable = t1.payable)The need to optimize rises from a bad design.My articles[^]
Thank U Mika, I want retain Rows(1,2). With ur query Rows (1,2,4,5) are getting eliminated. As rows 4,5 are having same data in other way I don't want to display it. If I show relation b/w Bank 'm' & 'n' I need not to Display the same relation b/w 'n' & 'm'. ------ Kiran
-
Thank U Mika, I want retain Rows(1,2). With ur query Rows (1,2,4,5) are getting eliminated. As rows 4,5 are having same data in other way I don't want to display it. If I show relation b/w Bank 'm' & 'n' I need not to Display the same relation b/w 'n' & 'm'. ------ Kiran
Yes, that's correct. That query finds matching pairs and eliminates both of them. Now you would have to add the logic which one of the matching rows is left in the result set. You didn't mention the logic why rows 1 and 2 are left so I wasn't able to write it to the example. Is it perhaps the issuedbankid why the rows are left or something else?
The need to optimize rises from a bad design.My articles[^]
-
Yes, that's correct. That query finds matching pairs and eliminates both of them. Now you would have to add the logic which one of the matching rows is left in the result set. You didn't mention the logic why rows 1 and 2 are left so I wasn't able to write it to the example. Is it perhaps the issuedbankid why the rows are left or something else?
The need to optimize rises from a bad design.My articles[^]
Sorry For not responding in time. I want to get a report of In the following way, If the user selects a "Bank" from dropdownlist, then he must get the list of all banks' amnts (may payable,receivable) towards that selected bank assume selected banks' Id is 1 (xyz bank) bankname Payable to(xyz) receivable from(xyz) aaa 1000 2000 bbb 2000 00.00 ccc 3000 4000 I have given the structure of my view. so how to generate this sort of report. what would be the query? I tried with this <pre>select T1.IssuedBank,T1.PaidBank,T1.Payable,T1.Receivable from Test_MergeView as T1 Left Join Test_MergeView as T2 ON T1.IssuedBank = T2.PaidBank and T1.PaidBank = T2.IssuedBank Where T1.PaidBank > T1.IssuedBank </pre> but if paidbankId is < issuedbankid that info is getting eliminated in display. to avoid rows 4,5 I used where clause. but other wanted info is also getting eliminated. ------ kiran
-
Sorry For not responding in time. I want to get a report of In the following way, If the user selects a "Bank" from dropdownlist, then he must get the list of all banks' amnts (may payable,receivable) towards that selected bank assume selected banks' Id is 1 (xyz bank) bankname Payable to(xyz) receivable from(xyz) aaa 1000 2000 bbb 2000 00.00 ccc 3000 4000 I have given the structure of my view. so how to generate this sort of report. what would be the query? I tried with this <pre>select T1.IssuedBank,T1.PaidBank,T1.Payable,T1.Receivable from Test_MergeView as T1 Left Join Test_MergeView as T2 ON T1.IssuedBank = T2.PaidBank and T1.PaidBank = T2.IssuedBank Where T1.PaidBank > T1.IssuedBank </pre> but if paidbankId is < issuedbankid that info is getting eliminated in display. to avoid rows 4,5 I used where clause. but other wanted info is also getting eliminated. ------ kiran
So if your data was:
Row | IssuedBankId | paidBankId | Payable | Receivable
1 | 1 | 2 | 1000 | 2000
2 | 1 | 3 | 1100 | 3000
3 | 1 | 4 | 4000 | 5000
4 | 2 | 1 | 2000 | 1000
5 | 3 | 1 | 3000 | 1100
6 | 4 | 2 | 5000 | Null
7 | 5 | 4 | 1000 | Nulland you said that if the user select 1 for bankid, then you would like the result to be:
Row | IssuedBankId | paidBankId | Payable | Receivable
1 | 1 | 2 | 1000 | 2000
2 | 1 | 3 | 1100 | 3000
3 | 1 | 4 | 4000 | 5000Is that correct? Are all the rows and columns present? Since that would be simply:
SELECT *
FROM TableName
WHERE IssuedBankId = 1I suppose that this isn't what you're looking for?
The need to optimize rises from a bad design.My articles[^]
-
So if your data was:
Row | IssuedBankId | paidBankId | Payable | Receivable
1 | 1 | 2 | 1000 | 2000
2 | 1 | 3 | 1100 | 3000
3 | 1 | 4 | 4000 | 5000
4 | 2 | 1 | 2000 | 1000
5 | 3 | 1 | 3000 | 1100
6 | 4 | 2 | 5000 | Null
7 | 5 | 4 | 1000 | Nulland you said that if the user select 1 for bankid, then you would like the result to be:
Row | IssuedBankId | paidBankId | Payable | Receivable
1 | 1 | 2 | 1000 | 2000
2 | 1 | 3 | 1100 | 3000
3 | 1 | 4 | 4000 | 5000Is that correct? Are all the rows and columns present? Since that would be simply:
SELECT *
FROM TableName
WHERE IssuedBankId = 1I suppose that this isn't what you're looking for?
The need to optimize rises from a bad design.My articles[^]
-
Thank U Mika, U made my job simpler , Though I was not searching for that, with ur previous sln I can proceed further. ------ Kiran